<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Disaster Recovery - Replicate databases to Recovery & NoRecovery Modes simultaneously

Published on
8,996 Points
2,496 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

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month