31 Days of Disaster Recovery

Today’s post took longer to prepare than I had anticipated which is why day 13 is being published on day 14. This won’t derail the 31 Days of Disaster Recovery series, even if it runs over into February. Day 13′s topic is standard backup scripts.

I’ve said on numerous occasions that the first thing a DBA should do when they inherit a new server is to make sure it has sufficient backups on it. Then later once everything is under control, circle back around and make sure the backup plan meets the needs of the restore plan and recovery SLAs. To facilitate that, I’ve used scripts that I wrote that I call my Standard Backups Scripts. Within a few seconds, I have a full backup plan put into place.

The scripts and documentation can be downloaded as a single zip file: StandardBackups.zip (26 KB)

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training

Standard Backups Script Details

These scripts are for creating and deploying standardized backups. This set of scripts can be used to manage full/differential backups and log backups. The scripts are robust in nature and will automatically process all databases as appropriate on the server.

The scripts can be deployed as is without any modifications. At the same time, a lot effort was put into making the scripts customizable for most scenarios. All parameters are well commented inline and in the SQL jobs that they create.

Deploy Backups.sql

DeployBackups.sql contains all other backup scripts together. You can download and execute this single script, and you are done deploying backups to your server. You can download the individual scripts if you prefer, but be sure to create all of the procedures before creating the jobs.

dba_BackupDBs.sql

All of the below default configuration options are customizable. The default configuration for this procedure is as follows:

• Back up all online databases on the server
  • Can specify a single database via @DBName parameter
• Make the best determination for the location of the backup files (if not passed in to procedure)
  • Use default backup location, if exists
  • Use location of last backup taken on server, if exists
  • Can specify a location via the @BackupLocation parameter
• Follow a schedule of a weekly full backup with daily differential backups
  • Use the @BackupType and @DayOfFullBackup parameters to customize this schedule
• The weekly full backup will occur on Friday night
  • Customizable via @DayOfFullBackup parameter
• All system databases (except tempdb) will always have a full backup performed
• Will default to using default server setting for compression
  • Customize via @UseCompression parameter
• Alerts of failures will not be sent via email
  • Enable via @SendAlerts parameter
  • Requires that @AlertRecipients parameter is also customized
  • Also requires that Database Mail is enabled and configured
• There are no default recipients for the alerts
  • Set via @AlertRecipients parameter
• The backups will be executed
  • Use @Debug parameter to output code instead of running backups
• When performing a differential backup, if a full backup does not exist, if will skip the backup and treat the attempt as a failure
  • Use @CreateFullIfNotExists parameter to create a full backup instead if one does not exist

For log backups:

To use this for log backups, you must customize at least 1 parameter, @BackupType. @BackupType = 2 will back up the log of all online databases that are not log shipping participants. Without further customization of the parameters, the procedure will perform the following:

• Back up the log of all online databases that are not participating in log shipping and are in the full or bulk-logged recovery model
  • Can specify a single database via @DBName parameter
• Make the best determination for the location of the backup files (if not passed in to procedure)
  • Use default backup location, if exists
  • Use location of last backup taken on server, if exists
  • Can specify a location via the @BackupLocation parameter
• Will default to using default server setting for compression
  • Customize via @UseCompression parameter
• Alerts of failures will not be sent via email
  • Enable via @SendAlerts parameter
  • Requires that @AlertRecipients parameter is also customized
  • Also requires that Database Mail is enabled and configured
• There are no default recipients for the alerts
  • Set via @AlertRecipients parameter
• The backups will be executed
  • Use @Debug parameter to output code instead of running backups
• When performing a log backup, if a full backup does not exist, if will skip the backup and treat the attempt as a failure
  • Use @CreateFullIfNotExists parameter to create a full backup instead if one does not exist

The customizable parameters are:

• @DBName sysname – Database name or null for all databases
• @BackupLocation nvarchar(255) – Location where you want the backups
• @BackupType bit – 0 = Full, 1 = Differential, 2 = Log, Null = Follow daily schedule (weekly full, daily diff)
• @UseCompression tinyint – 0 = Never use compression, 1 = Always use compression, Null = Do not specify compression, allow default server setting
• @DayOfFullBackup tinyint – Only applied if @BackupType is null. Use DatePart(dw, getdate()) to determine current day’s value, Sunday = 1, Saturday = 7
• @CreateFullIfNotExist bit – If full backup doesn’t exist, create full backup instead of differential or log backup, 0 = do not create, 1 = create
• @SendAlerts bit – 0 = do not send alerts, 1 = send alerts
• @AlertRecipients varchar(500) – Email address(es) to whom email alerts should go
• @Debug bit – 0 = execute backup, 1 = output the code without executing

dba_DeleteDBBackups.sql

All of the below default configuration options are customizable. The default configuration for this procedure is as follows:

• Delete all old backups that exceed the configured retention level
• Make the best determination for the location of the backup files
  • Customizable via @BackupLocation parameter
• Delete full\differential backups with the file extension of “bak”
  • Customizable via @FileExtension parameter
• Delete log backups with the file extension of “trn”
  • Customizable via @LogFileExtension parameter
• Retain only 1 full backup and all differential and log backups created since the full backup
  • Customizable via @Retention parameter
• The backups will be deleted
  • Use @Debug parameter to output code instead of running backups

The customizable parameters are:

• @BackupLocation nvarchar(255) – Location of the backups
• @FileExtension nvarchar(3) – File extension of full/differential backups
• @LogFileExtension nvarchar(3) – File extension of log backups
• @Retention int – The number of full backups to retain. All differentials since the oldest backup will be retained as well
• @Debug bit – 0 = execute deletion of backups, 1 = output the code without executing

BackupDatabase_job.sql

This script creates the jobs to execute the database backup procedures. The jobs result in the following commands being executed:

• Exec dbo.dba_DeleteDBBackups;
  • Daily at 1 AM
• Exec dbo.dba_BackupDBs;
  • Daily at 1 AM
• Exec dbo.dba_BackupDBs @BackupType = 2;
  • Every half hour

The scripts and documentation can be downloaded as a single zip file: StandardBackups.zip (26 KB)