Day 13 of 31 Days of Disaster Recovery: Standard Backup Scripts

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.

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

Kelly

I appreciate this series of blogs on DR. I have always tried to be very conscious of this, but I am learning new things from your posts and finding it useful to be reminded of the big picture. Thank you!

I haven’t had a chance to take a close look at your scripts yet, but I was wondering if you knew the significant differences between your scripts and the maintenance scripts regularly published by Ola Hallengren.

Thanks Kelly! I’m not sure what all Ola’s script does. I’ve always written my own maintenance scripts, and I’ve never used his. From what I understand, he has spent considerably more time adding functionality from user requests than I have, so I imagine that his has quite a bit of functionality that mine doesn’t, but I’m just speculating.