SQL Server Disaster Recovery Test

This article describes a simple procedure we implemented to ensure that the data on our the database on the Disaster Recovery server is consistent with the production server. Our DR Server is a separate server which is located in a separate data centre. The DR server receives data from the production database server via Transactional Log Shipping. If the database on the Production Server is named as ABC; then the DR Copy for this on the DR Server will be named ABC_DR which is always in NORECOVERY mode. Note that in Transactional Log Shipping the DR copy is always kept in NORECOVERY mode so that additional Transactional Log Backups can be applied over it. Also the Production Server as well as the DR Server are both in the same domain.

On the DR Server, I normally use the following RAID Configurations for SAN drives:

  • Since tempdb is a very High Read/Write intensive database, the RAID level to be used is 10.
  • Performance is not a primary concern for a backup drive and therefore I would recommend RAID 5 for that.
  • Since the performance of the database is dependent upon the speed of the Transactional Log file, I would recommend RAID 10.
  • For Data drives, RAID 5 is a good candidate.

To ensure that the Transactional Log Shipping is working, I scheduled a T-SQL script to run using a SQL Server Agent Job on the Production Database Server which will alert the DBA team if the Transactional Log Shipping goes out of sync. Please refer to the below article for more details on this.


Prior to running the test, logon to the Production instance and open the Transactional Log Shipping monitor and examine if the status is ‘Good’.

SQL Server Transactional Log Shipping has 3 types of Jobs :

  • Backup Job – Hosted on Production Instance.
  • Copy Job – Hosted on DR Instance.
  • Restore Job – Hosted on the DR instance.

Manually execute each of the 3 jobs in a sequence to ensure that the DR Copy of the database on the DR server has Point in Time data in it.

Once all the 3 jobs are executed in a sequential order, the DR server should have up-to-date data. Next you will need to break the Transactional Log Shipping.

On the Primary Server (i.e. the Production Server) right-click the database named ABC (i.e the db whose Log Shipping we need to break), then go to Properties and select Transactional Log Shipping and uncheck the check box as shown below.

Next, connect to the master database on the DR server and execute the below T-SQL to bring the database named ABC_DR online.


Once Log Shipping is broken, we first need to create a T-SQL job for taking a Differential and Transactional Log Backup along with a manual Full Backup of the main ABC database.

A differential backup will contain all the changes which have happened since the last Full Backup. I typically follow the practice of scheduling this every 3 hours on the Primary Server, the main advantage of having a Differential Backup in place is that it allows a faster recovery of databases in case of a disaster.

The below two scripts schedule Differential and Transactional Log Backups.

Differential Backup Script

Log Backup Script

Once the above step is completed, we normally follow a practice of renaming the DR database – in this case ABC_DR as ABC situated on the Secondary Server by performing the following tasks:

  1. Execute the below T-SQL query against the master database on the Secondary Server:
    alter database ABC_DR
    set single_user with rollback immediate

    This will set the database named ABC_DR to Single User mode so that all the existing sessions in it are killed and we can rename the database as shown in Step 2 below.
  2. EXEC sp_renamedb 'ABC_DR', 'ABC'
  3. Ensure that the database named ABC on the DR server will come ONLINE and multiple users will be able to access it :
  4. Ensure that all the SQL Server Logins are mapped appropriately to the database named ABC on the DR database. Also ensure that you use a separate login for both the Production and the DR databases to ensure that there is no security threat.

After the logins are mapped on the DR instance we then need to perform a Reindexing and Update Statistics operation against the database named ABC on the DR instance to improve its performance by executing the below two T-SQL scripts against it.

ReIndexing Script

Differential Backup Script

Please let us know in the comments if you have any feedback or suggestions, alternatively you can contact me at singhsatnam84@yahoo.com.


Leave a comment

Your email address will not be published.