If you missed any of the earlier posts in my DR series, you can check them out here:
- 31 Days of disaster Recovery
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- When are Checksums Written to a Page
- How to CHECKDB Like a Boss
- How Much Log Can a Backup Log
- The Case of the Backups That Wouldn’t Restore
- Who Deleted That Data?
- Which DBCC CHECK Commands Update Last Known Good DBCC
- Restoring Differential Backups With New Files
- Handling Corruption in a Clustered Index
- Improving Performance of Backups and Restores
- The Mysterious Case of the Long Backup
- Restoring Part of a Database
- Recovering SQL if the Tempdb Drive Dies
Restoring Replicated Databases
My friend’s current process for resetting his test replication environment was to drop replication, restore the publisher and subscriber, and then re-setup replication from scratch. This was time consuming for him, and he didn’t have the replication knowledge to automate the process. He had done some research and was considering using the sync with backup option for the distributor and publisher databases so that he could just restore them and they would work. He would still have to restore the subscription database or reinitialize the subscriber to get replication to work
I made the alternate suggestion of using database snapshots instead of regular backups for the restore of the replication databases. Since this is in a non-production environment, they are using Developer Edition even if they are using Standard Edition in productions. The trick is to create the snapshot of the publisher, distributor, and subscriber at the same time while there is no activity occurring and replication is in sync. Then when you need to reset the test environment, simply revert (restore) all 3 databases back to the snapshots at the same time. As far as replication knows, nothing has happened and everything is perfectly in sync. This process requires no replication knowledge to automate as it’s simply creating 3 database snapshots and then reverting the databases. And of course, both the snapshot creation and restore are sub-second processes adding less than a second to the turn-around of the test databases.
I sent him some sample code of how to do it:
Use master; -- Create database snapshots of all 3 replication databases -- distributor: Create Database distribution_snap On (Name = 'distribution', FileName = 'D:Program FilesMicrosoft SQL ServerMSSQL11.SQL12MSSQLDatadistribution.ndf') As Snapshot of distribution; Go -- Publisher Create Database Publisher_snap On (Name = 'Publisher', FileName = 'D:Program FilesMicrosoft SQL ServerMSSQL11.SQL12MSSQLDataPublisher.ndf') As Snapshot of Publisher; Go -- Subscriber Create Database Subscriber_snap On (Name = 'Subscriber', FileName = 'D:Program FilesMicrosoft SQL ServerMSSQL11.SQL12MSSQLDataSubscriber.ndf') As Snapshot of Subscriber; Go -- Restore databases as revert to database snapshots -- Need to ensure that no connections exist in databases -- distributor: Use distribution; Alter Database distribution Set Single_User With Rollback Immediate; Use master; Restore Database distribution From Database_Snapshot = 'distribution_snap'; Go -- Publisher Use Publisher; Alter Database Publisher Set Single_User With Rollback Immediate; Use master; Restore Database Publisher From Database_Snapshot = 'Publisher_snap'; Go -- Subscriber Use Subscriber; Alter Database Subscriber Set Single_User With Rollback Immediate; Use master; Restore Database Subscriber From Database_Snapshot = 'Subscriber_snap'; Go
It is important that the code for the revert to the database snapshot is run as a single batch. Don’t run it command by command. If you pause long enough between the point where you switch to master database after changing the database to single-user, you give time for other processes to get the single connection to the database, and the restore will be blocked. Just run the whole thing as a single batch.
Day 30 of 31 Days of Disaster Recovery (T-SQL Tuesday #40): Using Partial Availability and Initialize from Backup to Replicate a Partial Database | SQLSoldier
[…] Using Database Snapshots to Restore Replicated Databases in Test […]