Practical SQL Server

A Replacement for Maintenance Plan Backups

This blog post is a bit of a ‘repeat’ – since I already covered much of the rationale behind this post in Part 23 of my multi-part series on AlwaysOn Availability Groups and SQL Server Agent Jobs. But, I also figured that this is enough of an important topic to merit its own blog post.

SQL Server Maintenance Plans – The Good and The Bad (Mostly Bad)

As I opined in the post mentioned above, many DBAs absolutely abhor SQL Server Maintenance Plans. And, of a truth – the older and more experienced I get, the more I can’t help but think (at best) that most of the ‘Tasks’ available to Maintenance Plans were coded by interns – or people with no actual clue of SQL Server best practices. Take the Rebuild Index Task for example – there’s absolutely no way to specify at what level of fragmentation a Rebuild should be incurred – meaning that if you set this up against a specific database, this task will rebuild ALL indexes regardless of whether they need it or not. Accidentally run it back to back (i.e., two times in a row) and it’ll simply rebuild all of your indexes – twice.

Accordingly, while most of the Tasks available for inclusion in Maintenance Plans suck, I’ve always sort of been impressed by the underlying engine that Maintenance Plans use – because it’s quite powerful and capable. Which, of course, falls squarely in the “no duh” category – because we’re talking about SSIS here. That, and I’ve always somewhat liked the logic and implementation provided by Back Up Database Tasks. Yeah, they’re nothing close to what you’d get with some of the third party offerings out there – and, arguably, Olla Hallengren’s scripts are better in a lot of ways. BUT, Maintenance Plan backups also have a certain simplicity and ‘ease of logic’ that I’ve always enjoyed. For example, the ability to drop backups for a given database into its own folder is a big win for me. (If I’m ever in the middle of a disaster and don’t want to use the GUI/Backup History to try and figure out which files I need to use, having all of the files in a big ‘pig pile’ has never been a big win in my book.) Likewise, the ability to set up very simple ‘cleanup’ routines for expired backups (via the Maintenance Cleanup Task) has also been a big win in my book – as there’s something pretty clean and obvious about being able simply keep database backups for N number of days (or hours).

Replacing Maintenance Plan Backups with a Script

But, Maintenance Plans also come with their own ugliness and baggage – which is that it’s hard (but not impossible) to assign ownership to Maintenance Plans to the SysAdmin account. And, as powerful as SSIS is, the idea of ‘migrating’ Maintenance Plans from one server to another (or keeping them synchronized between multiple servers) is a big enough pain in the butt that (for these and other reasons) I went ahead a while back and whipped up some scripts that provide – more or less – functional parity with the key benefits that Maintenance Plan backups provide – all without, well, the Maintenance Plans.

Here’s the one I use for NON SQL Server Express instances:

/*

	-- This sproc replaces SQL Server Maintenance Task Backups. 
	-- It just requires a path, a list of dbs to backup, and the kind of backup... 
	--		as well as a timestamp for stuff older than X to delete. 

	-- NOTE: on Express and Web ... COMPRESSION isn't supported. 

	-- System Database Backups: 
	DECLARE @olderThan datetime; 
	SET @olderThan = DATEADD(dd, -3, GETDATE());

	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'FULL', 
		@DatabasesToBackup = '[SYSTEM_DBS]', 
		@BackupDirectory = 'D:\SQLBackups\System',
		@OlderBackupDeletionTime = @olderThan;
	GO

	-- Full backups of ALL User Databases: 
	DECLARE @olderThan datetime; 
	SET @olderThan = DATEADD(hh, -48, GETDATE());
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'FULL', 
		@DatabasesToBackup = '[USER_DBS]', 
		@BackupDirectory = 'D:\SQLBackups\User',
		@OlderBackupDeletionTime = @olderThan;
	GO

	-- Full backups of SPECIFIED User Databases: 
	DECLARE @olderThan datetime; 
	SET @olderThan = DATEADD(hh, 25, GETDATE());
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'FULL', 
		@DatabasesToBackup = 'meddling,ssv2', 
		@BackupDirectory = 'D:\SQLBackups\User',
		@OlderBackupDeletionTime = @olderThan;
	GO

	-- DIFF backups of SPECIFIED Databases:
	DECLARE @olderThan datetime; 
	SET @olderThan = DATEADD(hh, -48, GETDATE());
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'DIFF', 
		@DatabasesToBackup = 'meddling,ssv2', 
		@BackupDirectory = 'D:\SQLBackups\User',
		@OlderBackupDeletionTime = @olderThan;
	GO

	-- T-Log Backups of all User DBs: 
	DECLARE @olderThan datetime; 
	SET @olderThan = DATEADD(hh, -36, GETDATE());
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'LOG', 
		@DatabasesToBackup = '[USER_DBS]', 
		@BackupDirectory = 'D:\SQLBackups\User',
		@OlderBackupDeletionTime = @olderThan;
	GO

*/


USE master;
GO

IF OBJECT_ID('dbo.dba_DatabaseBackups','P') IS NOT NULL
	DROP PROC dbo.dba_DatabaseBackups;
GO

CREATE PROC dbo.dba_DatabaseBackups
	@BackupType					sysname, 
	@DatabasesToBackup			nvarchar(1000), 
	@BackupDirectory			sysname, 
	@OlderBackupDeletionTime	datetime, 
	@PrintOnly					bit		= 0
AS
	SET NOCOUNT ON;

	DECLARE @jobStart datetime; 
	SET @jobStart = GETDATE();

	-- verify 
	IF UPPER(@BackupType) NOT IN ('FULL', 'DIFF','LOG') BEGIN 
		PRINT 'Usage: @BackupType = FULL|DIFF|LOG';
		RAISERROR('Invalid @BackupType Specified.', 16, 1);
	END

	IF @OlderBackupDeletionTime >= GETDATE() BEGIN 
		RAISERROR('Invalid @OlderBackupDeletionTime - greater than or equal to NOW.', 16, 1); 
	END

	-- determine databases: 
	DECLARE @targetDatabases TABLE ( 
		database_name sysname NOT NULL
	); 

	IF UPPER(@DatabasesToBackup) = '[SYSTEM_DBS]' BEGIN 
		INSERT INTO @targetDatabases (database_name)
		SELECT 'master' UNION SELECT 'msdb' UNION SELECT 'model';
	END 

	IF UPPER(@DatabasesToBackup) = '[USER_DBS]' BEGIN 

		IF @BackupType = 'LOG'
			INSERT INTO @targetDatabases (database_name)
			SELECT name FROM sys.databases 
			WHERE recovery_model_desc = 'FULL' 
				AND name NOT IN ('master', 'model', 'msdb', 'tempdb') 
			ORDER BY name;
		ELSE 
			INSERT INTO @targetDatabases (database_name)
			SELECT name FROM sys.databases 
			WHERE name NOT IN ('master', 'model', 'msdb','tempdb') 
			ORDER BY name;
	END 

	IF (SELECT COUNT(*) FROM @targetDatabases) <= 0 BEGIN 
		-- deserialize the list of databases to backup: 
		SELECT TOP 400 IDENTITY(int, 1, 1) as N 
		INTO #Tally
		FROM sys.columns;

		DECLARE @SerializedDbs nvarchar(1200);
		SET @SerializedDbs = ',' + REPLACE(@DatabasesToBackup, ' ', '') + ',';

		INSERT INTO @targetDatabases (database_name)
		SELECT SUBSTRING(@SerializedDbs, N + 1, CHARINDEX(',', @SerializedDbs, N + 1) - N - 1)
		FROM #Tally
		WHERE N < LEN(@SerializedDbs) 
			AND SUBSTRING(@SerializedDbs, N, 1) = ',';

		IF @BackupType = 'LOG' BEGIN
			DELETE FROM @targetDatabases 
			WHERE database_name NOT IN (
				SELECT name FROM sys.databases WHERE recovery_model_desc = 'FULL'
			);
		  END
		ELSE 
			DELETE FROM @targetDatabases
			WHERE database_name NOT IN (SELECT name FROM sys.databases);
	END

	-- verify that we've got something: 
	IF (SELECT COUNT(*) FROM @targetDatabases) <= 0 BEGIN 
		PRINT 'Usage: @DatabasesToBackup = [SYSTEM_DBS]|[USER_DBS]|dbname1,dbname2,dbname3,etc';
		RAISERROR('No databases for backup.', 16, 1);
	END

	-- normalize path: 
	IF(RIGHT(@BackupDirectory, 1) = '\')
		SET @BackupDirectory = LEFT(@BackupDirectory, LEN(@BackupDirectory) - 1);

	-- Begin the backups:
	DECLARE backups CURSOR FAST_FORWARD FOR 
	SELECT 
		database_name 
	FROM 
		@targetDatabases
	ORDER BY 
		database_name;

	DECLARE @currentDB sysname;
	DECLARE @backupPath sysname;
	DECLARE @backupStatement nvarchar(2000);
	DECLARE @backupName sysname; 
	DECLARE @now datetime;
	DECLARE @timestamp sysname;
	DECLARE @extension sysname;
	DECLARE @offset sysname;
	DECLARE @verifyStatement nvarchar(2000);
	DECLARE @Errors TABLE ( 
		ErrorID int IDENTITY(1,1) NOT NULL, 
		[Database] sysname NOT NULL, 
		ErrorMessage nvarchar(2000)
	);
	DECLARE @ErrorMessage sysname;

	OPEN backups;
	FETCH NEXT FROM backups INTO @currentDB; 

	WHILE @@FETCH_STATUS = 0 BEGIN 
		
		SET @backupPath = @BackupDirectory + N'\' + @currentDB; 

		-- make sure the subdirectory exists: 
		IF @PrintOnly = 1 BEGIN 
			PRINT 'Verify/Create Directory: ' + @backupPath;
		  END 
		ELSE
			EXECUTE master.dbo.xp_create_subdir @backupPath;

		-- create a backup name: 
		SET @extension = '.bak';
		IF @BackupType = 'LOG'
			SET @extension = '.trn';

		SET @now = GETDATE();
		SET @timestamp = REPLACE(REPLACE(REPLACE(CONVERT(sysname, @now, 120), '-','_'), ':',''), ' ', '_');
		SET @offset = RIGHT(CAST(CAST(RAND() AS decimal(12,11)) AS varchar(20)),7);

		SET @backupName = @currentDB + '_backup_' + @timestamp + '_' + @offset + @extension;

		-- primary diff between this and a maintenance plan backup is: CHECKSUM... 
		SET @backupStatement = 'BACKUP {0} ' + QUOTENAME(@currentDB, '[]') + ' TO DISK = N''' + @backupPath + '\' + @backupName + ''' 
	WITH {1} COMPRESSION, NOFORMAT, NOINIT, NAME = N''' + @backupName + ''', SKIP, REWIND, NOUNLOAD, CHECKSUM, STATS = 25;'

		IF @BackupType IN ('FULL', 'DIFF') BEGIN 
			SET @backupStatement = REPLACE(@backupStatement, '{0}', 'DATABASE');

			IF @BackupType = 'DIFF'
				SET @backupStatement = REPLACE(@backupStatement, '{1}', 'DIFFERENTIAL,');
			ELSE
				SET @backupStatement = REPLACE(@backupStatement, '{1}', '');
		  END
		ELSE BEGIN -- log file backup
			SET @backupStatement = REPLACE(@backupStatement, '{0}', 'LOG');
			SET @backupStatement = REPLACE(@backupStatement, '{1}', '');
		END

		SET @verifyStatement = 'RESTORE VERIFYONLY FROM DISK = N''' + @backupPath + '\' + @backupName + ''' WITH NOUNLOAD, NOREWIND;';

		BEGIN TRY
			IF @PrintOnly = 1 BEGIN 
				PRINT @backupStatement;
				PRINT @verifyStatement;
			  END
			ELSE BEGIN 
				EXEC sp_executesql @backupStatement;
				EXEC sp_executesql @verifyStatement;
			END

		END TRY 
		BEGIN CATCH
			SELECT @ErrorMessage = ERROR_MESSAGE();

			INSERT INTO @Errors ([Database], ErrorMessage)
			VALUES  (@currentDB, @ErrorMessage);
		END CATCH


		FETCH NEXT FROM backups INTO @currentDB; 
	END;

	CLOSE backups;
	DEALLOCATE backups;

	-- Now cleanup any/all files as needed: 
	DECLARE @deleteStatement nvarchar(2000); 
	SET @deleteStatement = 'EXECUTE master.dbo.xp_delete_file 0, N''' + @BackupDirectory + ''', N''' + REPLACE(@extension, '.','') + ''', N''' + REPLACE(CONVERT(nvarchar(20), @OlderBackupDeletionTime, 120), ' ', 'T') + ''', 1;';

	BEGIN TRY 
		IF @PrintOnly = 1 
			PRINT @deleteStatement
		ELSE
			EXEC sp_executesql @deleteStatement; 
	END TRY 
	BEGIN CATCH
		SELECT @ErrorMessage = ERROR_MESSAGE();

		INSERT INTO @Errors ([Database], ErrorMessage)
		VALUES  ('File Deletion', @ErrorMessage);
	END CATCH

	IF (SELECT COUNT(*) FROM @Errors) > 0 BEGIN 
		PRINT 'The Following Errors were Detectected: ';

		DECLARE errors CURSOR FAST_FORWARD FOR 
		SELECT [Database],[ErrorMessage] 
		FROM @Errors
		ORDER BY ErrorID;

		OPEN errors; 
		FETCH NEXT FROM errors INTO @currentDB, @ErrorMessage;

		WHILE @@FETCH_STATUS = 0 BEGIN 
			PRINT 'DATABASE/OPERATION: ' + @currentDB + ' -> ' + @ErrorMessage;
			
			FETCH NEXT FROM errors INTO @currentDB, @ErrorMessage;
		END 

		CLOSE errors;
		DEALLOCATE errors;

		-- Raise an error so we know there were problems:
		RAISERROR('Unexpected errors executing backups - see output.', 16, 1);
	END

	RETURN 0;
GO

As you can see, it’s pretty straight forward in that you specify which databases or types of databases you want to backup – and what kind of backup you want to run, along with path info and ‘retention’ details. Otherwise, it’ll ‘spit out’ backups that are virtually indistinguishable from what you’d get using Maintenance Plan Backup functionality (right down to the filenames themselves). Though, if you poke around enough, you’ll note that it’s missing SOME features and capabilities. (If those are big enough need on your end – feel free to implement as needed.)

I have, actually, used a similar approach for executing backups against SQL Server Express backups – but, in those scenarios, actually removed the WITH COMPRESS option (of course) AND went ahead and changed the sproc’s signature – to make it a bit easier to access via .bat or .ps1 files:

/*

	-- This sproc replaces SQL Server Maintenance Task Backups. 
	-- It just requires a path, a list of dbs to backup, and the kind of backup... 
	--		as well as a timestamp for stuff older than X hours to delete. 

	-- System Database Backups: 
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'FULL', 
		@DatabasesToBackup = '[SYSTEM_DBS]', 
		@BackupDirectory = 'D:\SQLBackups\System',
		@CleanupTime = 72;
	GO

	-- Full backups of ALL User Databases: 
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'FULL', 
		@DatabasesToBackup = '[USER_DBS]', 
		@BackupDirectory = 'D:\SQLBackups\User',
		@CleanupTime = 48;
	GO

	-- Full backups of SPECIFIED User Databases: 
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'FULL', 
		@DatabasesToBackup = 'meddling,ssv2', 
		@BackupDirectory = 'D:\SQLBackups\User',
		@CleanupTime = 25;
	GO

	-- DIFF backups of SPECIFIED Databases:
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'DIFF', 
		@DatabasesToBackup = 'meddling,ssv2', 
		@BackupDirectory = 'D:\SQLBackups\User',
		@CleanupTime = 48;
	GO

	-- T-Log Backups of all User DBs: 
	EXEC dbo.dba_DatabaseBackups
		@BackupType = 'LOG', 
		@DatabasesToBackup = '[USER_DBS]', 
		@BackupDirectory = 'D:\SQLBackups\User',
		@CleanupTime = 36;
	GO

*/


USE master;
GO

IF OBJECT_ID('dbo.dba_DatabaseBackups','P') IS NOT NULL
	DROP PROC dbo.dba_DatabaseBackups;
GO

CREATE PROC dbo.dba_DatabaseBackups
	@BackupType					sysname, 
	@DatabasesToBackup			nvarchar(1000), 
	@BackupDirectory			sysname, 
	@CleanupTime				int		= 72,  -- in hours... 
	@PrintOnly					bit		= 0
AS
	SET NOCOUNT ON;

	DECLARE @jobStart datetime; 
	SET @jobStart = GETDATE();

	-- verify 
	IF UPPER(@BackupType) NOT IN ('FULL', 'DIFF','LOG') BEGIN 
		PRINT 'Usage: @BackupType = FULL|DIFF|LOG';
		RAISERROR('Invalid @BackupType Specified.', 16, 1);
	END

	-- translate the hours setting:
	DECLARE @OlderBackupDeletionTime datetime;
	SET @OlderBackupDeletionTime = DATEADD(hh, 0 - @CleanupTime, GETDATE());


	IF @OlderBackupDeletionTime >= GETDATE() BEGIN 
		RAISERROR('Invalid @OlderBackupDeletionTime - greater than or equal to NOW.', 16, 1); 
	END

	-- determine databases: 
	DECLARE @targetDatabases TABLE ( 
		database_name sysname NOT NULL
	); 

	IF UPPER(@DatabasesToBackup) = '[SYSTEM_DBS]' BEGIN 
		INSERT INTO @targetDatabases (database_name)
		SELECT 'master' UNION SELECT 'msdb' UNION SELECT 'model';
	END 

	IF UPPER(@DatabasesToBackup) = '[USER_DBS]' BEGIN 

		IF @BackupType = 'LOG'
			INSERT INTO @targetDatabases (database_name)
			SELECT name FROM sys.databases 
			WHERE recovery_model_desc = 'FULL' 
				AND name NOT IN ('master', 'model', 'msdb', 'tempdb') 
			ORDER BY name;
		ELSE 
			INSERT INTO @targetDatabases (database_name)
			SELECT name FROM sys.databases 
			WHERE name NOT IN ('master', 'model', 'msdb','tempdb') 
			ORDER BY name;
	END 

	IF (SELECT COUNT(*) FROM @targetDatabases) <= 0 BEGIN 
		-- deserialize the list of databases to backup: 
		SELECT TOP 400 IDENTITY(int, 1, 1) as N 
		INTO #Tally
		FROM sys.columns;

		DECLARE @SerializedDbs nvarchar(1200);
		SET @SerializedDbs = ',' + REPLACE(@DatabasesToBackup, ' ', '') + ',';

		INSERT INTO @targetDatabases (database_name)
		SELECT SUBSTRING(@SerializedDbs, N + 1, CHARINDEX(',', @SerializedDbs, N + 1) - N - 1)
		FROM #Tally
		WHERE N < LEN(@SerializedDbs) 
			AND SUBSTRING(@SerializedDbs, N, 1) = ',';

		IF @BackupType = 'LOG' BEGIN
			DELETE FROM @targetDatabases 
			WHERE database_name NOT IN (
				SELECT name FROM sys.databases WHERE recovery_model_desc = 'FULL'
			);
		  END
		ELSE 
			DELETE FROM @targetDatabases
			WHERE database_name NOT IN (SELECT name FROM sys.databases);
	END

	-- verify that we've got something: 
	IF (SELECT COUNT(*) FROM @targetDatabases) <= 0 BEGIN 
		PRINT 'Usage: @DatabasesToBackup = [SYSTEM_DBS]|[USER_DBS]|dbname1,dbname2,dbname3,etc';
		RAISERROR('No databases for backup.', 16, 1);
	END

	-- normalize path: 
	IF(RIGHT(@BackupDirectory, 1) = '\')
		SET @BackupDirectory = LEFT(@BackupDirectory, LEN(@BackupDirectory) - 1);

	-- Begin the backups:
	DECLARE backups CURSOR FAST_FORWARD FOR 
	SELECT 
		database_name 
	FROM 
		@targetDatabases
	ORDER BY 
		database_name;

	DECLARE @currentDB sysname;
	DECLARE @backupPath sysname;
	DECLARE @backupStatement nvarchar(2000);
	DECLARE @backupName sysname; 
	DECLARE @now datetime;
	DECLARE @timestamp sysname;
	DECLARE @extension sysname;
	DECLARE @offset sysname;
	DECLARE @verifyStatement nvarchar(2000);
	DECLARE @Errors TABLE ( 
		ErrorID int IDENTITY(1,1) NOT NULL, 
		[Database] sysname NOT NULL, 
		ErrorMessage nvarchar(2000)
	);
	DECLARE @ErrorMessage sysname;

	OPEN backups;
	FETCH NEXT FROM backups INTO @currentDB; 

	WHILE @@FETCH_STATUS = 0 BEGIN 
		
		SET @backupPath = @BackupDirectory + N'\' + @currentDB; 

		-- make sure the subdirectory exists: 
		IF @PrintOnly = 1 BEGIN 
			PRINT 'Verify/Create Directory: ' + @backupPath;
		  END 
		ELSE
			EXECUTE master.dbo.xp_create_subdir @backupPath;

		-- create a backup name: 
		SET @extension = '.bak';
		IF @BackupType = 'LOG'
			SET @extension = '.trn';

		SET @now = GETDATE();
		SET @timestamp = REPLACE(REPLACE(REPLACE(CONVERT(sysname, @now, 120), '-','_'), ':',''), ' ', '_');
		SET @offset = RIGHT(CAST(CAST(RAND() AS decimal(12,11)) AS varchar(20)),7);

		SET @backupName = @currentDB + '_backup_' + @timestamp + '_' + @offset + @extension;

		-- primary diff between this and a maintenance plan backup is: CHECKSUM... 
		SET @backupStatement = 'BACKUP {0} ' + QUOTENAME(@currentDB, '[]') + ' TO DISK = N''' + @backupPath + '\' + @backupName + ''' 
	WITH {1} NOFORMAT, NOINIT, NAME = N''' + @backupName + ''', SKIP, REWIND, NOUNLOAD, CHECKSUM;'

		IF @BackupType IN ('FULL', 'DIFF') BEGIN 
			SET @backupStatement = REPLACE(@backupStatement, '{0}', 'DATABASE');

			IF @BackupType = 'DIFF'
				SET @backupStatement = REPLACE(@backupStatement, '{1}', 'DIFFERENTIAL,');
			ELSE
				SET @backupStatement = REPLACE(@backupStatement, '{1}', '');
		  END
		ELSE BEGIN -- log file backup
			SET @backupStatement = REPLACE(@backupStatement, '{0}', 'LOG');
			SET @backupStatement = REPLACE(@backupStatement, '{1}', '');
		END

		SET @verifyStatement = 'RESTORE VERIFYONLY FROM DISK = N''' + @backupPath + '\' + @backupName + ''' WITH NOUNLOAD, NOREWIND;';

		BEGIN TRY
			IF @PrintOnly = 1 BEGIN 
				PRINT @backupStatement;
				PRINT @verifyStatement;
			  END
			ELSE BEGIN 
				EXEC sp_executesql @backupStatement;
				EXEC sp_executesql @verifyStatement;
			END

		END TRY 
		BEGIN CATCH
			SELECT @ErrorMessage = ERROR_MESSAGE();

			INSERT INTO @Errors ([Database], ErrorMessage)
			VALUES  (@currentDB, @ErrorMessage);
		END CATCH


		FETCH NEXT FROM backups INTO @currentDB; 
	END;

	CLOSE backups;
	DEALLOCATE backups;

	-- Now cleanup any/all files as needed: 
	DECLARE @deleteStatement nvarchar(2000); 
	SET @deleteStatement = 'EXECUTE master.dbo.xp_delete_file 0, N''' + @BackupDirectory + ''', N''' + REPLACE(@extension, '.','') + ''', N''' + REPLACE(CONVERT(nvarchar(20), @OlderBackupDeletionTime, 120), ' ', 'T') + ''', 1;';

	BEGIN TRY 
		IF @PrintOnly = 1 
			PRINT @deleteStatement
		ELSE
			EXEC sp_executesql @deleteStatement; 
	END TRY 
	BEGIN CATCH
		SELECT @ErrorMessage = ERROR_MESSAGE();

		INSERT INTO @Errors ([Database], ErrorMessage)
		VALUES  ('File Deletion', @ErrorMessage);
	END CATCH

	IF (SELECT COUNT(*) FROM @Errors) > 0 BEGIN 
		PRINT 'The Following Errors were Detectected: ';

		DECLARE errors CURSOR FAST_FORWARD FOR 
		SELECT [Database],[ErrorMessage] 
		FROM @Errors
		ORDER BY ErrorID;

		OPEN errors; 
		FETCH NEXT FROM errors INTO @currentDB, @ErrorMessage;

		WHILE @@FETCH_STATUS = 0 BEGIN 
			PRINT 'DATABASE/OPERATION: ' + @currentDB + ' -> ' + @ErrorMessage;
			
			FETCH NEXT FROM errors INTO @currentDB, @ErrorMessage;
		END 

		CLOSE errors;
		DEALLOCATE errors;

		-- Raise an error so we know there were problems:
		RAISERROR('Unexpected errors executing backups - see output.', 16, 1);
	END

	RETURN 0;
GO

Using an @CleanupTime (in hours) makes it so I don’t have to run an additional query/operation to set up an @olderThan – as with the previous script. Which, in turn, means I can keep everything I need to call this ‘SQL Express’ version from a single line. For example, here’s exactly what I’d drop into a .bat file to create full backups of user databases on a SQL Server Express instance:

REM FULL Backups of User DBs:
osql -S. -E -Q "EXEC dbo.dba_DatabaseBackups @BackupType = 'FULL', @DatabasesToBackup = '[USER_DBS]', @BackupDirectory = 'D:\SQLBackups\User', @CleanupTime = 72;"

T-Log backups would be the same – but with an @BackupType of ‘LOG’ – then, it’s just a question of setting up a job via the Windows Task Scheduler to handle execution. 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish