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:SQLBackupsSystem',@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:SQLBackupsUser',@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:SQLBackupsUser',@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:SQLBackupsUser',@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:SQLBackupsUser',@OlderBackupDeletionTime = @olderThan;GO*/USE master;GOIF OBJECT_ID('dbo.dba_DatabaseBackups','P') IS NOT NULLDROP PROC dbo.dba_DatabaseBackups;GOCREATE PROC dbo.dba_DatabaseBackups@BackupTypesysname, @DatabasesToBackupnvarchar(1000), @BackupDirectorysysname, @OlderBackupDeletionTimedatetime, @PrintOnlybit= 0ASSET 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);ENDIF @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 #TallyFROM 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 #TallyWHERE N < LEN(@SerializedDbs) AND SUBSTRING(@SerializedDbs, N, 1) = ',';IF @BackupType = 'LOG' BEGINDELETE FROM @targetDatabases WHERE database_name NOT IN (SELECT name FROM sys.databases WHERE recovery_model_desc = 'FULL');  ENDELSE DELETE FROM @targetDatabasesWHERE 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 @targetDatabasesORDER 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 ELSEEXECUTE 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,');ELSESET @backupStatement = REPLACE(@backupStatement, '{1}', '');  ENDELSE BEGIN -- log file backupSET @backupStatement = REPLACE(@backupStatement, '{0}', 'LOG');SET @backupStatement = REPLACE(@backupStatement, '{1}', '');ENDSET @verifyStatement = 'RESTORE VERIFYONLY FROM DISK = N''' + @backupPath + '' + @backupName + ''' WITH NOUNLOAD, NOREWIND;';BEGIN TRYIF @PrintOnly = 1 BEGIN PRINT @backupStatement;PRINT @verifyStatement;  ENDELSE BEGIN EXEC sp_executesql @backupStatement;EXEC sp_executesql @verifyStatement;ENDEND TRY BEGIN CATCHSELECT @ErrorMessage = ERROR_MESSAGE();INSERT INTO @Errors ([Database], ErrorMessage)VALUES  (@currentDB, @ErrorMessage);END CATCHFETCH 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 @deleteStatementELSEEXEC sp_executesql @deleteStatement; END TRY BEGIN CATCHSELECT @ErrorMessage = ERROR_MESSAGE();INSERT INTO @Errors ([Database], ErrorMessage)VALUES  ('File Deletion', @ErrorMessage);END CATCHIF (SELECT COUNT(*) FROM @Errors) > 0 BEGIN PRINT 'The Following Errors were Detectected: ';DECLARE errors CURSOR FAST_FORWARD FOR SELECT [Database],[ErrorMessage] FROM @ErrorsORDER 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);ENDRETURN 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:SQLBackupsSystem',@CleanupTime = 72;GO-- Full backups of ALL User Databases: EXEC dbo.dba_DatabaseBackups@BackupType = 'FULL', @DatabasesToBackup = '[USER_DBS]', @BackupDirectory = 'D:SQLBackupsUser',@CleanupTime = 48;GO-- Full backups of SPECIFIED User Databases: EXEC dbo.dba_DatabaseBackups@BackupType = 'FULL', @DatabasesToBackup = 'meddling,ssv2', @BackupDirectory = 'D:SQLBackupsUser',@CleanupTime = 25;GO-- DIFF backups of SPECIFIED Databases:EXEC dbo.dba_DatabaseBackups@BackupType = 'DIFF', @DatabasesToBackup = 'meddling,ssv2', @BackupDirectory = 'D:SQLBackupsUser',@CleanupTime = 48;GO-- T-Log Backups of all User DBs: EXEC dbo.dba_DatabaseBackups@BackupType = 'LOG', @DatabasesToBackup = '[USER_DBS]', @BackupDirectory = 'D:SQLBackupsUser',@CleanupTime = 36;GO*/USE master;GOIF OBJECT_ID('dbo.dba_DatabaseBackups','P') IS NOT NULLDROP PROC dbo.dba_DatabaseBackups;GOCREATE PROC dbo.dba_DatabaseBackups@BackupTypesysname, @DatabasesToBackupnvarchar(1000), @BackupDirectorysysname, @CleanupTimeint= 72,  -- in hours... @PrintOnlybit= 0ASSET 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 #TallyFROM 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 #TallyWHERE N < LEN(@SerializedDbs) AND SUBSTRING(@SerializedDbs, N, 1) = ',';IF @BackupType = 'LOG' BEGINDELETE FROM @targetDatabases WHERE database_name NOT IN (SELECT name FROM sys.databases WHERE recovery_model_desc = 'FULL');  ENDELSE DELETE FROM @targetDatabasesWHERE 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 @targetDatabasesORDER 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 ELSEEXECUTE 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,');ELSESET @backupStatement = REPLACE(@backupStatement, '{1}', '');  ENDELSE BEGIN -- log file backupSET @backupStatement = REPLACE(@backupStatement, '{0}', 'LOG');SET @backupStatement = REPLACE(@backupStatement, '{1}', '');ENDSET @verifyStatement = 'RESTORE VERIFYONLY FROM DISK = N''' + @backupPath + '' + @backupName + ''' WITH NOUNLOAD, NOREWIND;';BEGIN TRYIF @PrintOnly = 1 BEGIN PRINT @backupStatement;PRINT @verifyStatement;  ENDELSE BEGIN EXEC sp_executesql @backupStatement;EXEC sp_executesql @verifyStatement;ENDEND TRY BEGIN CATCHSELECT @ErrorMessage = ERROR_MESSAGE();INSERT INTO @Errors ([Database], ErrorMessage)VALUES  (@currentDB, @ErrorMessage);END CATCHFETCH 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 @deleteStatementELSEEXEC sp_executesql @deleteStatement; END TRY BEGIN CATCHSELECT @ErrorMessage = ERROR_MESSAGE();INSERT INTO @Errors ([Database], ErrorMessage)VALUES  ('File Deletion', @ErrorMessage);END CATCHIF (SELECT COUNT(*) FROM @Errors) > 0 BEGIN PRINT 'The Following Errors were Detectected: ';DECLARE errors CURSOR FAST_FORWARD FOR SELECT [Database],[ErrorMessage] FROM @ErrorsORDER 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);ENDRETURN 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:SQLBackupsUser', @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.