Backup all Databases Using SQL LiteSpeed

,

This script backs up all databases or a specified database to a disk location. Databases can be explicitly excluded from backup as well.  Can be used for Full, Differential or Transaction Log Backups.  Can also dynamically create sub directories for each database, as well as perform DBCC and restore verify operations.  Includes LiteSpeed support for multi-threading and encryption.

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:

Rate

Share

Share

Rate