Real Disaster Recovery Of SQL Databases

PART 1 – Automatic Restoration of backup files to a failover server

SQL Servers’ norecovery mode keeps the database stable and ready to accept the changes you’re progressively applying as part of the backup process. This means that it’s only necessary to apply the latest differential or log backup before the database is ready to be accessed by users and applications.

The disaster recovery method used is to have a ‘hot’ standby server (SQL2), which is already installed, stable and, most importantly, is an exact copy of the production server’s configuration. The standby server should already have the most recent operational databases fully-restored in norecovery mode.

Implementing a Hot Standby Server

After SQL Server has been installed on the failover server, you need to check that Robocopy is installed in the sysroot\windows\system32 folder. Secondly, Red Gate’s SQL Backup software must connect to the server and be configured by clicking the small grey square next to server listing in left pane – this is for instance auto-configuration, if it has not been done already.

791-SQB_installingservercomponents.gif

Figure 1 – SQL Backup’s auto-configuration system.

Robocopy is much better than ( the soon-to-be-discontinued) Xcopy, by the way. And since Windows Server 2003, Robocopy has been the recommended / future-proofed tool of choice. As far as I know, Xcopy will no longer be available in future versions of Windows Server.

Next, for the stored procedures that execute Robocopy (we place these procedures in a local database on each server called DBA_tools), you need to allow the advanced option xp_cmdshell to run:

Screenshot (6).png

In order to copy the backup files, each database on the standby server needs a database-specific SQL Server Agent job running Robocopy at the required interval to copy full and differential backups from the production server to the standby server. These jobs can be run at whatever frequency needed, be it daily, hourly or even more often if your operations require it.

Robocopy is the first step in all automated restore jobs, unless you want to add validation steps prior to the backup file copy. The following example copies all differential database backups from a production server to a DRP server:

Screenshot (7).png

A database-specific SQL Server Job will restore these backups daily to the hot standby server (DRP) using stored procedures specifically created for this setup, such as:

  • usp_DB_Restore_Master or usp_DB_Restore_Master_Multi

  • usp_DB_Restore

  • usp_DB_Restore_NoRecovery

  • usp_DB_Restore_differential

  • usp_DB_Restore_Log

A consideration for the DBA regarding the level of database recovery

If you are currently in Simple Recovery mode, and provided there are regular Transaction Log and differential backups (as in, several times a day), you can switch your recovery model over to Bulk-Logged in production to restore up to a specific point in time. This will naturally minimize the amount of data lost from the work session prior to any downtime.

Full Recovery mode is recommended for critical databases that require auditing compliance.

In the event of failure, the most recent log or differential backup is ready to be applied to the standby database sitting in norecovery mode, and you’re up and running quickly with minimal down-time.

An alternative method for a much smaller database, where the total restore time is below five minutes, is to apply the complete restore every hour to the failover server, in which case you don’t need to worry about norecovery mode.

By Hugo Shebbeare, 28 August 2009.



Jon Bossman