http://www.sqlservercentral.com/blogs/robert_davis/2013/01/15/day-13-of-31-days-of-disaster-recovery-standard-backup-scripts/

Printed 2014/08/29 10:48AM

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

By Robert Davis, 2013/01/15

31 Days of Disaster Recovery

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:

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:

The customizable parameters are:

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

The customizable parameters are:

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

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.