31 Days of Disaster Recovery

Summary

Restoring data to or immediately before a specific transaction is a key tool in data recovery. If you can identify the LSN of the transaction that deleted data that shouldn’t have been deleted, you can use this process to restore the database to the last point immediately prior to the delete. More than likely, you will be restoring it to a different name and then using INSERT … SELECT queries to restore the data to the live database.

Download the demo script: Demo_ConvertLSNs.zip (1 KB)

Day 12: Extreme Disaster Recovery Training

Fittingly, today’s focus on disaster recovery as part of my 31 Days of Disaster Recovery is to announce a full day of training, Extreme Disaster Recovery, being offered at SQLBits XI. This training will be the only pre-conference session being led by two (2) Microsoft Certified Masters (SQL Server). I will be delivering this session along with my good friend and frequent cohort Argenis Fernandez (blog|@DBArgenis) with whom I have delivered 3 pre-conference sessions already.

Extreme Disaster Recovery

SQL Bits XI – Extreme Disaster Recovery Here’s your chance to learn data recovery from the big dogs. Two Microsoft Certified Masters (MCMs) will take your Recovery game to an all new level. Take a deep dive into SQL Server recovery and learn how to handle a wide variety of data loss and corruption scenarios. The session will cover how to be prepared for, prevent, and recover data lost due to deletion or corruption.

Learn the following skills in this session:

• 
  Built-in functionality in SQL Server for preventing and detecting corruption that you may not even know about
  
• 
  How to identify a specific transaction in the transaction log and recover data lost from that transaction
  
• 
  Categories of corruption and how to manage recovery differently for each one
  

Don’t come empty handed. Bring your laptops, and we’ll practice recovering corrupt databases together.

Register here for SQLBits and a Pre-con

Day 13: Standard Backup Scripts

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)

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 fulldifferential 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)