Day 29 of 31 Days of Disaster Recovery: Using Database Snapshots to Restore Replicated Databases in Test

For day 29 of my 31 Days of Disaster Recover series, I want to talk about restoring replicated databases from database snapshots. Someone asked me recently if I had any recommendations for speeding up their unit testing on replicated databases. He has to run unit tests over and over on replicated databases to ensure that they deploy successfully in a replicated environment. His current process was adding too much time to the process and automating it was difficult for him because he doesn’t know replication to a great depth. My recommendation was to use database snapshots.

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training
13. Standard Backup Scripts
14. Fixing a Corrupt Tempdb
15. Running DBCC CheckTable in Parallel Jobs
16. Disaster Recovery Gems From Around The Net
17. When are Checksums Written to a Page
18. How to CHECKDB Like a Boss
19. How Much Log Can a Backup Log
20. The Case of the Backups That Wouldn’t Restore
21. Who Deleted That Data?
22. Which DBCC CHECK Commands Update Last Known Good DBCC
23. Restoring Differential Backups With New Files
24. Handling Corruption in a Clustered Index
25. Improving Performance of Backups and Restores
26. The Mysterious Case of the Long Backup
27. Restoring Part of a Database
28. 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 Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Data\distribution.ndf')
    As Snapshot of distribution;
Go

-- Publisher
Create Database Publisher_snap
    On (Name = 'Publisher',
        FileName = 'D:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Data\Publisher.ndf')
    As Snapshot of Publisher;
Go

-- Subscriber
Create Database Subscriber_snap
    On (Name = 'Subscriber',
        FileName = 'D:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Data\Subscriber.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 ge tthe single connection to the database, and the restore will be blocked. Just run the whole thing as a single batch.