Backup all Databases Using SQL LiteSpeed

USE msdb
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'prc_DoSLSBackup' AND type = 'P')
DROP PROC prc_DoSLSBackup
GO
--
-- prc_DoSLSBackup
-- Authors: Jeffrey Aven, Imceda Software, Inc.
-- 
-- Revision History:
-- 07/02 – 1.0 (Initial Version)
-- 11/02 - 1.1  Added Differential Backup Functionality
-- 22/03 - 1.2  Added Support for Exclusion of Databases
-- 24/09 - 1.3   Added Error Handling for Concurrent Backup Operations
-- 15/10 - 1.4   Fixed Imtermittent Issue with Excluded DBs 
-- Continues backing up after DBCC error returns error at the end of the procedure
-- 16/10 - 1.5   Added support for creating subdirectory for each database
-- 28/11 - 1.501 Added support for logging
-- 03/12 - 1.502 Added support for servername directory


CREATE PROCEDURE prc_DoSLSBackup
@BackupType char(1) = 'F' 
--'D' for Differential Backups
--'F' for Full Database Backup (Default)
--'L' for Transaction Log Backup
,@DBName sysname = '*'
--'*' for Backup All Databases (Default)
--'<database_name>' to backup an individual database 
,@BackupDir varchar(1024)
--Directory to store SQL LiteSpeed Backups
,@DoVerify bit = 1
--1 = Perform Verification of SQL LIteSpeed Backups (Default)
--0 = Skip Verification
,@UseCmdLine bit = 0
--1 = Use SQL LiteSpeed Command Line Interface
--0 = Use SQL LiteSpeed Extended Stored Procedure Interface (Default)
,@Debug int = 0
--2 = Print verbose logging and generate SQL LiteSpeed log files
--1 = Print verbose logging
--0 = Minimal logging (Default)
,@EncryptionKey varchar(1024) = NULL
--Encryption Key used to secure SQL LiteSpeed Backup Devices (Optional)
,@Threads int = NULL
--Number of Threads to use for SQL LiteSpeed Backup, dynamically determined if not supplied
,@Priority int = NULL
--Base priority of SQL LiteSpeed Backup process, dynamically determined if not supplied
,@RetainDays int = NULL
--Number of days to retain backup device files, if supplied backup files older than the number of days specified 
--will be purged
,@InitBackupDevice bit = 0
--1 = Reinitialize backup device without date time stamp 
--0 = Create a new device for each backup which has the date time stamp embedded in the file name (Default)
,@PerformDBCC bit = 1
--1 = Perform DBCC CHECKDB prior to backing up database (default) 
--0 = Do not Perform DBCC CHECKDB prior to backing up database
,@ExcludedDBs varchar(2048) = NULL
--Comma Delimited List of Databases in Double Quotes to be Excluded From Backup Operation
--CAUTION: Ensure proper syntax (eg '"pubs","Northwind"')
,@create_sub_dir bit = 0
--Creates a subdirectory under the backup directory for each db being backed up
,@create_server_dir bit = 0
--Creates a directory under the backup directory for the current server, used for scenarios where muliple servers 
--are backing up to the same location, ensures no namespace conflicts

AS
SET NOCOUNT ON
--Local Variables
DECLARE @DBStatus int
,@DBMode varchar(50)
,@StatusMsgPrefix char(18)
,@StatusMsg varchar(1024)
,@PhyName varchar(1024)
,@BackupType2 varchar(4)
,@Cmd varchar(1024)
,@RC int
,@dbid int
,@BackupStartTime varchar(12)
,@BackupAllDBs bit
,@Operation varchar(6)
,@CmdStr varchar(255)
,@BUFile varchar(255)
,@BUFileDate char(12)
,@BUFileDate2 smalldatetime
,@BaseBUFileDatalength int
,@sqlstr varchar(1048)
,@is_db_excluded bit
,@orig_backup_dir varchar(512)

SELECT @StatusMsgPrefix =  'prc_DoSLSBackup : ' 
SELECT @BackupAllDBs = 0
SELECT @orig_backup_dir = @BackupDir

--Check Access Level
IF IS_SRVROLEMEMBER ( 'sysadmin') = 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Insufficient system access for ' + SUSER_SNAME() 
+ ' to perform SQLLiteSpeed backup'
RAISERROR(@StatusMsg,16,1)
RETURN 1 
END

--Validate @BackupType Argument
IF @BackupType IN ('L', 'F', 'D')
BEGIN
IF @BackupType = 'L'
SELECT @BackupType2 = 'Log'
IF @BackupType = 'F'
SELECT @BackupType2 = 'Full'
IF @BackupType = 'D'
SELECT @BackupType2 = 'Diff'
END
ELSE
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Valid parameters for @BackupType are L,F,D' 
RAISERROR(@StatusMsg,16,1)
RETURN 1 
END

--Validate @DBName Argument
IF @DBName <> '*'
BEGIN
IF NOT EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @DBName)
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Invalid database selected for @DBName (' + @DBName 
+ ') parameter' 
RAISERROR(@StatusMsg,16,1)
RETURN 1 
END 
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + @DBName + ' Selected for ' + @BackupType2 + ' Backup' 
PRINT @StatusMsg
END
GOTO DoBackup
END
ELSE
BEGIN
SELECT @BackupAllDBs = 1
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' All Databases Selected for ' + @BackupType2 + ' Backup' 
PRINT @StatusMsg
END
END

DECLARE DBs CURSOR FOR
SELECT name, dbid, status
FROM master..sysdatabases
WHERE [name] <> 'tempdb'
FOR READ ONLY

OPEN DBs
FETCH NEXT FROM DBs INTO @DBName, @dbid, @DBStatus
WHILE @@FETCH_STATUS = 0
BEGIN
--Is Databases Explicitly Excluded
IF @ExcludedDBs IS NOT NULL
BEGIN
SELECT @ExcludedDBs = REPLACE(@ExcludedDBs,'"',char(39))
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [name] = 'tmp_is_db_excluded')
DROP TABLE tempdb..tmp_is_db_excluded

CREATE TABLE tempdb..tmp_is_db_excluded
(
is_db_excluded bit 
)
-- J. O'Brien need to reset bit field to 0 as if tmp_is_db_excluded is empty
SELECT @is_db_excluded = 0
SELECT @sqlstr = 'IF ' + char(39) + @DBName + char(39) + ' IN (' + @ExcludedDBs  + ') INSERT tempdb..tmp_is_db_excluded SELECT 1'
EXEC (@sqlstr)
SELECT @is_db_excluded = is_db_excluded FROM tempdb..tmp_is_db_excluded
IF @is_db_excluded = 1
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Skippping ' + @DBName + ' as this database has been explicitly excluded' 
PRINT @StatusMsg
END
GOTO NextDB
END
END
DoBackup:
SELECT @Operation = 'Backup'
SELECT @BackupStartTime = CONVERT(varchar(12),GETDATE(),12) + REPLACE(CONVERT(varchar(12),GETDATE(),8),':','')
IF @BackupType = 'L'
BEGIN
--Check for System Database
IF @dbid <= 4
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'System Database (' + @DBName 
+ ') skipped for transaction log backup' 
PRINT @StatusMsg
END
IF @BackupAllDBs = 1
GOTO NextDB
ELSE
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Unable to backup ' + @DBName 
+ ' - System Database' 
RAISERROR(@StatusMsg,16,1)
RETURN 1
END
END 
--Check for Simple Recovery Model
IF @DBStatus & 8 <> 0
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Database (' + @DBName 
+ ') skipped for transaction log backup - Simple Recovery Model' 
PRINT @StatusMsg
END
IF @BackupAllDBs = 1
GOTO NextDB
ELSE
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Unable to backup ' 
+ @DBName + ' - Simple Recovery Model' 
RAISERROR(@StatusMsg,16,1)
RETURN 1
END 
END
END

 --Check Database Accessibility
SELECT @DBMode = 'OK'
IF DATABASEPROPERTY(@DBName, 'IsDetached') > 0 
SELECT @DBMode = 'Detached'
ELSE IF DATABASEPROPERTY(@DBName, 'IsInLoad') > 0 
SELECT @DBMode = 'Loading'
ELSE IF DATABASEPROPERTY(@DBName, 'IsNotRecovered') > 0 
SELECT @DBMode = 'Not Recovered'
ELSE IF DATABASEPROPERTY(@DBName, 'IsInRecovery') > 0 
SELECT @DBMode = 'Recovering'
ELSE IF DATABASEPROPERTY(@DBName, 'IsSuspect') > 0 
SELECT @DBMode = 'Suspect'
ELSE IF DATABASEPROPERTY(@DBName, 'IsOffline') > 0  
SELECT @DBMode = 'Offline'
ELSE IF DATABASEPROPERTY(@DBName, 'IsEmergencyMode') > 0 
SELECT @DBMode = 'Emergency Mode'
ELSE IF DATABASEPROPERTY(@DBName, 'IsShutDown') > 0 
SELECT @DBMode = 'Shut Down (problems during startup)'
IF @DBMode <> 'OK'
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Unable to backup ' + @DBName 
+ ' - Database is in '  + @DBMode + ' state'
PRINT @StatusMsg
END
IF @BackupAllDBs = 1
GOTO NextDB
ELSE
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Unable to backup ' + @DBName 
+ ' - Database is in '  + @DBMode + ' state'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END
END
--Check if Backup Directory Exists
IF @create_server_dir = 1 and @create_sub_dir = 1
SELECT @BackupDir = @orig_backup_dir + '\' + @@SERVERNAME + '\' + @DBName
IF @create_server_dir = 1 and @create_sub_dir = 0
SELECT @BackupDir = @orig_backup_dir + '\' + @@SERVERNAME
IF @create_server_dir = 0 and @create_sub_dir = 1
SELECT @BackupDir = @orig_backup_dir + '\' + @DBName

SELECT @Cmd = 'dir "' + @BackupDir + '"' 
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
IF @RC <> 0
--Create Backup Directory
BEGIN
SELECT @Cmd = 'md "' + @BackupDir + '"'
EXEC @RC = master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
IF @RC <> 0
BEGIN 
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Unable to create backup directory (' 
+ @BackupDir + ')'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END
END

--Build the Backup File Name
SELECT @PhyName = @BackupDir + '\' + REPLACE(@@SERVERNAME,'\','_')  + '.' + REPLACE(@DBName,'.','_') 
+ '.'+ @BackupType2 
IF @InitBackupDevice = 0
--New Device For Each Backup
SELECT @PhyName = @PhyName + @BackupStartTime + '.SLS' 
ELSE
--Re-Initialize Each Backup Device
SELECT @PhyName = @PhyName + '.SLS' 

--Set Tuning Defaults
IF @Threads IS NULL
BEGIN
CREATE TABLE #MSVer
(
[Index] int
,[Name] varchar (255)
,Internal_Value int NULL
,Charater_Value varchar(255)
)
INSERT #MSVer EXEC master..xp_msver
SELECT @Threads = Internal_Value FROM #MSVer WHERE [Name] = 'ProcessorCount'
DROP TABLE #MSVer
END
IF @Priority IS NULL
SELECT @Priority = 0
--Do DBCC CHECKDB
IF @PerformDBCC = 1
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing DBCC CHECKDB on Database ' + @DBName 
+ char(10)
PRINT @StatusMsg
DBCC CHECKDB (@DBName)
END
ELSE
DBCC CHECKDB (@DBName) WITH NO_INFOMSGS
SET @RC = @@ERROR
IF @RC <> 0
BEGIN
SELECT @Operation = 'DBCC CHECKDB'
GOTO FailedBackup
END
END

--Do Backup
IF @BackupType = 'F'
--Perform Full Database Backup
BEGIN
IF @UseCmdLine = 1
BEGIN
SELECT @Cmd = char(34) + 'C:\Program Files\DBAssociates\SQLLiteSpeed\SQLLiteSpeed.EXE' + char(34) 
+ ' -S' + @@SERVERNAME
+ ' -BDatabase'
+ ' -D' + @DBName
+ ' -F' + @PhyName
+ ' -t' + CONVERT(varchar(2),@Threads)
+ ' -p' + CONVERT(varchar(2),@Priority)
+ ' -I'
+ ' -T'  
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + ' -K' + @EncryptionKey
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10) 
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @Cmd
END
ELSE
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE
BEGIN
SELECT @Cmd = 'EXEC master..xp_backup_database' + char(10)
+ char(9) + '@database = ' + char(39) + @DBName + char(39) + char(10)
+ char(9) + ', @filename = ' + char(39) + @PhyName + char(39) + char(10)
+ char(9) + ', @threads = ' + CONVERT(varchar(2),@Threads) + char(10)
+ char(9) + ', @priority = ' + CONVERT(varchar(2),@Priority) + char(10)
+ char(9) + ', @init = 1' + char(10)
+ char(9) + ', @logging = ' + CONVERT(char(1),@Debug) + char(10)
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + char(9) + ', @encryptionkey = ' + char(39) 
+ @EncryptionKey + char(39) + char(10)
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' 
+ @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10) 
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
END
IF @EncryptionKey IS NOT NULL
EXEC @RC = master..xp_backup_database
@database = @DBName
,@filename = @PhyName 
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@encryptionkey = @EncryptionKey
,@logging = @Debug
ELSE
EXEC @RC = master..xp_backup_database
@database = @DBName
,@filename = @PhyName 
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@logging = @Debug
END
IF @RC <> 0
GOTO FailedBackup
END

IF @BackupType = 'D'
--Perform Differential Database Backup
BEGIN
IF @DBName = 'master'
GOTO NextDB
IF @UseCmdLine = 1
BEGIN
SELECT @Cmd = char(34) + 'C:\Program Files\DBAssociates\SQLLiteSpeed\SQLLiteSpeed.EXE' + char(34) 
+ ' -S' + @@SERVERNAME
+ ' -BDatabase'
+ ' -D' + @DBName
+ ' -F' + @PhyName
+ ' -t' + CONVERT(varchar(2),@Threads)
+ ' -p' + CONVERT(varchar(2),@Priority)
+ ' -I'
+ ' -T'  
+ ' -WDIFFERENTIAL'
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + ' -K' + @EncryptionKey
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10) 
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @Cmd
END
ELSE
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE
BEGIN
SELECT @Cmd = 'EXEC master..xp_backup_database' + char(10)
+ char(9) + '@database = ' + char(39) + @DBName + char(39) + char(10)
+ char(9) + ', @filename = ' + char(39) + @PhyName + char(39) + char(10)
+ char(9) + ', @threads = ' + CONVERT(varchar(2),@Threads) + char(10)
+ char(9) + ', @priority = ' + CONVERT(varchar(2),@Priority) + char(10)
+ char(9) + ', @init = 1' + char(10)
+ char(9) + ', @with = ' + char(39) + 'DIFFERENTIAL' + char(39) + char(10)
+ char(9) + ', @logging = ' + CONVERT(char(1),@Debug) + char(10)
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + char(9) + ', @encryptionkey = ' + char(39) 
+ @EncryptionKey + char(39) + char(10)
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10) 
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
END
IF @EncryptionKey IS NOT NULL
EXEC @RC = master..xp_backup_database
@database = @DBName
,@filename = @PhyName 
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@encryptionkey = @EncryptionKey
,@with = 'DIFFERENTIAL'
,@logging = @Debug
ELSE
EXEC @RC = master..xp_backup_database
@database = @DBName
,@filename = @PhyName 
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@with = 'DIFFERENTIAL'
,@logging = @Debug
END
IF @RC <> 0
BEGIN
IF @RC = 11704
BEGIN
GOTO NextDB
END
ELSE
BEGIN
GOTO FailedBackup
END
END
END

IF @BackupType = 'L'
--Perform Transaction Log Backup
BEGIN
IF @UseCmdLine = 1
BEGIN
SELECT @Cmd = char(34) + 'C:\Program Files\DBAssociates\SQLLiteSpeed\SQLLiteSpeed.EXE' + char(34) 
+ ' -S' + @@SERVERNAME
+ ' -BLog'
+ ' -D' + @DBName
+ ' -F' + @PhyName
+ ' -t' + CONVERT(varchar(2),@Threads)
+ ' -p' + CONVERT(varchar(2),@Priority)
+ ' -I'
+ ' -T'  
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + ' -K' + @EncryptionKey
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10) 
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @Cmd
END
ELSE
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE
BEGIN
SELECT @Cmd = 'EXEC master..xp_backup_log' + char(10)
+ char(9) + '@database = ' + char(39) + @DBName + char(39) + char(10)
+ char(9) + ', @filename = ' + char(39) + @PhyName + char(39) + char(10)
+ char(9) + ', @threads = ' + CONVERT(varchar(2),@Threads) + char(10)
+ char(9) + ', @priority = ' + CONVERT(varchar(2),@Priority) + char(10)
+ char(9) + ', @init = 1' + char(10)
+ char(9) + ', @logging = ' + CONVERT(char(1),@Debug) + char(10)
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + char(9) + ', @encryptionkey = ' + char(39) 
+ @EncryptionKey + char(39) + char(10)
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10) 
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
END
IF @EncryptionKey IS NOT NULL
EXEC @RC = master..xp_backup_log
@database = @DBName
,@filename = @PhyName 
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@encryptionkey = @EncryptionKey
,@logging = @Debug
ELSE
EXEC @RC = master..xp_backup_log
@database = @DBName
,@filename = @PhyName 
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@logging = @Debug
END
IF @RC <> 0
BEGIN
IF @RC = 11704
BEGIN
GOTO NextDB
END
ELSE
BEGIN
GOTO FailedBackup
END
END
END
--Verify Backup Device
IF @DoVerify = 1
BEGIN
IF @UseCmdLine = 1
BEGIN
SELECT @Cmd = char(34) + 'C:\Program Files\DBAssociates\SQLLiteSpeed\SQLLiteSpeed.EXE' + char(34) 
+ ' -S' + @@SERVERNAME
+ ' -RVerifyonly'
+ ' -F' + @PhyName
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + ' -K' + @EncryptionKey
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Verifyonly of Backup Device ' 
+ @PhyName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10) 
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @Cmd
END
ELSE
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE
BEGIN
SELECT @Cmd = 'EXEC master..xp_restore_verifyonly' + char(10)
+ char(9) + ' @filename = ' + char(39) + @PhyName + char(39) + char(10)
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + char(9) + ', @encryptionkey = ' + char(39) 
+ @EncryptionKey + char(39) + char(10)
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Verifyonly of Backup Device ' 
+ @PhyName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10) 
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
END
IF @EncryptionKey IS NOT NULL
EXEC @RC = master..xp_restore_verifyonly
@filename = @PhyName 
,@encryptionkey = @EncryptionKey
,@logging = @Debug
ELSE
EXEC @RC = master..xp_restore_verifyonly
@filename = @PhyName 
,@logging = @Debug
END
IF @RC <> 0
BEGIN
SELECT @Operation = 'Verify'
GOTO FailedBackup
END
END

--Delete Old Backup Files
IF @RetainDays IS NOT NULL AND @InitBackupDevice = 0
BEGIN
--Building up Table of Files to Delete
CREATE TABLE #DirOut
(
[Output] varchar(255)
)
SELECT @CmdStr = 'dir "' + @BackupDir + '\' + REPLACE(@@SERVERNAME,'\','_')  + '.' 
+ REPLACE(@DBName,'.','_') +  '.' + @BackupType2  + '*.SLS" /B'
SELECT @BaseBUFileDatalength = LEN(REPLACE(@@SERVERNAME,'\','_')  + '.' 
+ REPLACE(@DBName,'.','_') +  '.' + @BackupType2)
INSERT #DirOut EXEC master..xp_cmdshell @CmdStr
--Scroll Through Table
DECLARE BUFiles CURSOR FOR
SELECT [Output] FROM #DirOut WHERE [Output] IS NOT NULL
FOR READ ONLY
OPEN BUFiles
FETCH NEXT FROM BUFiles INTO @BUFile
WHILE @@FETCH_STATUS = 0
BEGIN
--Reconstruct DateTime From Filename
SELECT @BUFileDate = LEFT(REPLACE(SUBSTRING(@BUFile,@BaseBUFileDatalength + 1,LEN(@BUFile)),'.SLS',''),6)
SELECT @BUFileDate2 = CONVERT(smalldatetime,@BUFileDate,12)
--Compare Date
IF @BUFile <> (REPLACE(@@SERVERNAME,'\','_')  + '.' + REPLACE(@DBName,'.','_') + '.'
+ @BackupType2 + '.SLS')
BEGIN
IF DATEDIFF(d,@BUFileDate2,getdate()) > @RetainDays
BEGIN
SELECT @CmdStr = 'del "' + @BackupDir + '\' + @BUFile + '"'
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Deleting File : ' 
+ @BUFile
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix 
+ 'Command to be Executed : ' + @CmdStr
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @CmdStr 
END
ELSE
EXEC @RC = master..xp_cmdshell @CmdStr, NO_OUTPUT
END
END
FETCH NEXT FROM BUFiles INTO @BUFile
END
CLOSE BUFiles
DEALLOCATE BUFiles
DROP TABLE #DirOut
END

IF @BackupAllDBs = 0
GOTO NoCursor
ELSE
GOTO NextDB

FailedBackup:
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - ' + @Operation + ' Operation Failed for ' 
+ @BackupType + ' Backup of ' + @DBName
RAISERROR(@StatusMsg,16,1)
IF @BackupAllDBs = 0
GOTO NoCursor
ELSE
GOTO NextDB
NextDB:
FETCH NEXT FROM DBs INTO @DBName, @dbid, @DBStatus
END

CLOSE DBs
DEALLOCATE DBs

NoCursor: