EDIT: This stored procedure has been updated.
Yesterday, I posted a stored procedure that performs a full backup on the databases. I mentioned that I had a SQL LiteSpeed version. This version backs up all databases except master, model, msdb, tempdb, pubs, NorthWind. It has one input parameters, which is the path to where you would like to backup the databases. It'll create a sub directory underneath that path for each of the databases. It also deletes any files that are older than two days in that sub directory. If a sub directory doesn't exist, it creates one for you. It checks how many processors exist in the database server using xp_regread since SQL LiteSpeed can perform better on a multi-processor server. We want n-1, for n processors according to the documentation. I haven't checked if it fails when there is only one processor, since 1-1 equals 0 and @threads is 1 - 32. I just don't have a single processor server with SQL LiteSpeed installed.
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Backup_LS
--
-- AUTHOR : Tara Duggan
-- INPUTS : @Path - location of the backups
-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure performs a full backup on all of the user databases
--
-- EXAMPLES (optional) : EXEC isp_Backup_LS @Path = 'G:\MSSQL\Backup\'
----------------------------------------------------------------------------------------------------
CREATE PROC isp_Backup_LS
(@Path VARCHAR(100))
AS
SET NOCOUNT ON
DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @DBName SYSNAME -- stores the database name that is currently being processed
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @NumProcs INT -- stores the number of processors that the server has registered
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @desc VARCHAR(200) -- stores the description of the backup
DECLARE @filename VARCHAR(200) -- stores the path and file name of the bkp file
-- Get the list of the databases to be backed up, does not include master, model, msdb, tempdb, Northwind, or pubs
SELECT name
INTO #WhichDatabase
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'pubs', 'tempdb', 'Northwind')
ORDER BY name
-- Get the number of processors that the server has
EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Control\Session Manager',
@value_name = 'RegisteredProcessors',
@value = @NumProcs OUTPUT
-- We want n - 1 threads, where n is the number of processors
SELECT @NumProcs = @NumProcs - 1
-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase
SET @RowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN
-- Get the current date using style 120, remove all dashes, spaces, and colons
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
-- Build the .bkp path and file name
SELECT @filename = @Path + @DBName + '\' + @DBName + '_LS_Full_' + @Now + '.bkp'
-- Build the description of the backup
SELECT @desc = 'Full backup of ' + @DBName + ' - ' + CONVERT(VARCHAR(50), GETDATE())
-- Build the dir command that will check to see if the directory exists
SELECT @cmd = 'dir ' + @Path + @DBName
-- Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master.dbo.xp_cmdshell @cmd
-- If the directory does not exist, we must create it
IF @result <> 0
BEGIN
-- Build the mkdir command
SELECT @cmd = 'mkdir ' + @Path + @DBName
-- Create the directory
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
END
-- The directory exists, so let's delete files older than two days
ELSE
BEGIN
-- Stores the name of the file to be deleted
DECLARE @WhichFile VARCHAR(1000)
CREATE TABLE #DeleteOldFiles
(
DirInfo VARCHAR(7000)
)
-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @Path + @DBName + ' /OD'
-- Run the dir command and put the results into a temp table
INSERT INTO #DeleteOldFiles
EXEC master.dbo.xp_cmdshell @cmd
-- Delete all rows from the temp table except the ones that correspond to the files to be deleted
DELETE
FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%
%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - 2
-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles
SET @RowCnt = @@ROWCOUNT
-- Interate through the temp table until there are no more files to delete
WHILE @RowCnt <> 0
BEGIN
-- Build the del command
SELECT @cmd = 'del ' + @Path + + @DBName + '\' + @WhichFile + ' /Q /F'
-- Delete the file
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
-- To move to the next file, the current file name needs to be deleted from the temp table
DELETE
FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @WhichFile
-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles
SET @RowCnt = @@ROWCOUNT
END
DROP TABLE #DeleteOldFiles
END
-- Backup the database using xp_backup_database
EXEC master.dbo.xp_backup_database
@database = @DBName,
@filename = @filename,
@backupname = @DBName,
@desc = @desc,
@init = 1,
@threads = @NumProcs
-- To move onto the next database, the current database name needs to be deleted from the temp table
DELETE
FROM #WhichDatabase
WHERE name = @DBName
-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase
SET @RowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY '00:00:05'
END
DROP TABLE #WhichDatabase
SET NOCOUNT OFF
RETURN 0
GO