<

Disaster Recovery - Replicate databases to Recovery & NoRecovery Modes simultaneously

Published on
8,912 Points
2,412 Views
Last Modified:
Approved
Last month I joined with a new organization as a DBA and implementing disaster recovery was my first task.  I faced some issues while configuring high availability options to the databases.

Without knowing that Copy-only is configured on the databases, I configured Log Shipping on those databases and it started giving errors.  And after the investigation of the errors I came know the cause of the issues.

But as per our company structure we need to sync the database in recovery mode to a different server for QA team.

A question I posed at Experts-Exchange got me some good suggestions to help me configure replication, mirroring, etc.

To configure transactional replication, we don't have primary keys on all the tables, so the replication option is ruled out.  And mirroring will not work as per our requirements.

Finally we need to sync the production databases to different servers in recovery mode (Testing Team - at least in one server - needs to be in sync every 2 hours) and Non-recovery mode (Disaster recovery - to at least two servers -  needs to be synchronized every 15 minutes).

So I started this using scripting as per our requirement.   I created backup scripts, restore scripts and a table to track the process.  I am attaching the scripts to this article.  Here are some details about the attached scripts:

DR_FullBackups.sql
This procedure is created for taking full backups of all the databases or the given databases. This SP will be created on primary database servers.
DR_DiffBackups.sql
This procedure is created for taking differential backups of all the databases or the given databases. This SP will be created on primary database servers.
DR_LogBackups.sql
This procedure is created for taking transactional log backups of all the databases or the given databases. This SP will be created on primary database servers.
DR_FullBackupsForRestoresFailed.sql
This procedure is created to take full backups for the databases if the they are failed during restore process. This SP will be created on each secondary database servers.

Needs to be executed from secondary server.
BRInfo - Tables.sql
Table created for information and monitoring the disaster process like status of backup/copy/restore and time taken for backup/copy/restore.This table is created on all the secondary databases. Please check the attached Excel file for the sample structure of the table.
DR_CopyBackups.sql
This procedure is created to copy the backups to the secondary servers. This SP will be created on each secondary database servers.
DR_RestoreBackups.sql
This procedure is created to restore the backups to the secondary database servers. Databases are restored in NO-RECOVERY mode using this procedure. This SP will be created on each secondary database servers. Using this procedure we can restore FULL, DIFFERENTIAL & LOG backups.

To restore the backup in recovery mode.
DR_RestoreBackups - RM.sql
This procedure is created to restore the backups to the secondary database servers. Databases are restored in RECOVERY mode using this procedure. This SP will be created on each secondary database servers as per the requirement. Using this procedure we can restore only the FULL & DIFFERENTIAL backups. During the restore, all the processes running on the databases will be killed for smooth restore.
DR_DeleteBackups.sql
This procedure is created to delete all the previous day backups from the sharing folder & local folder. The above table is also purged during this process.

This SP will be created on one secondary database servers.
DR_DeleteBackups - RM.sql
This procedure is created to delete all the previous day backups from the local folder. The above table is also purged during this process. This SP will be created on all remaining secondary database servers.
Shrinking Log Files_SimpleFull.sql
This script is executed before full backups to truncate log from all the databases.

Similar to Logshipping, all the backup scripts will be executed on Primary Server and backup info will be stored in the table (BRInfo).

BRInfo will act as master table for copy, restore & deleting backups.

BRInfo table should be created on each secondary server. And copy, restore & delete scripts will be executed separately on each secondary server.

Please check the attached scripts.  Feel free to post any suggestion that you have for improvements.  Thanks!
Primary-Server-Scripts.zip
Secondary-Server-Scripts-for-NoR.zip
Secondary-Server-Scripts-for-Rec.zip
0
Comment
0 Comments

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month