Day 30 of 31 Days of Disaster Recovery (T-SQL Tuesday #40): Using Partial Availability and Initialize from Backup to Replicate a Partial Database

It’s been a tough and long road to 31 Days of Disaster Recovery. It’s been very difficult coming up with quality topic ideas for the series as we near the end. For day 30 of the series, I am combining a post on performing piecemeal restores with a post on filegroups for T-SQL Tuesday #40 and a post on replication.

In case you’re not familiar with T-SQL Tuesday, let me enlighten you. This blog entry is participating in T-SQL Tuesday #40, hosted this month by the Midnight DBA Jen McCown (Blog|@MidnightDBA|@JenniferMcCown). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: File and Filegroup Wisdom.

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
29. Using Database Snapshots to Restore Replicated Databases in Test

Replicating a Partial Database

Let’s assume a scenario where you have a very large database (VLDB) and you want to replicate only part of the database, but you don’t want to use a snapshot to initialize the subscription and you don’t want to copy the whole database backup to the subscriber and restore it. For example, if you have a 5 TB database with mostly historical, unchanging data and you only want to replicate a small percentage of the database. For this example, let’s assume that 3 TB of data is old data and 2 TB is current data and you only want to replicate the current data. How would you go about doing it in the fastest, easiest method?

Initialize from snapshot? Snapshots are slow and performance impacting. There is limited support for compression in snapshots, but it uses Windows CAB compression that can be CPU intensive and has limitations such as a 2 GB file limit. Maybe your production database can take that sort of performance hit, maybe not.

Initialize from backup? The trouble with this option is the documented way to do this is to back up the entire database, copy the entire database over, and restore the entire database. For 5 TB of data, this can take a really long time. Additionally, the entire database gets restored and if you don’t want the extra 3 TB of data in the database, you have to delete it yourself. Even more time and trouble to set up, not to mention the performance hit on the subscriber.

From Books Online:

A backup contains an entire database; therefore each subscription database will contain a complete copy of the publication database when it is initialized.
…
It is the responsibility of the administrator or application to remove any unwanted objects or data after the backup has been restored.

But there’s a better way. You can take the one-time hit of separating your tables to different filegroups (assuming they’re not already that way, and they’re probably not) in order to be able to set up replication in a much quicker and simpler way. Once you have the files segregated, you can use partial database availability to restore only the filegroups that contain the tables you want to replicate.

One of the caveats for doing this is that you have to backup the primary filegroup with any other filegroups you want to restore. The primary filegroup always has to be restored as it contains all of the system objects that define everything in the database, and the primary filegroups and any other filegroups must be based on the same recovery point, so they must be backed up together. You can perform a filegroup backup of filegroups by name or just the read/write filegroups which include all filegroups not marked as read-only. On the other hand, the restore can actually be performed from a full backup by specifying which filegroups to restore.

Any filegroups not restored on the subscriber will still be listed as part of the database, but the files in those filegroups will be listed as RECOVERY_PENDING. Any attempts to query tables in those filegroups will return an error stating that the file is not online. Then it’s a matter of setting up replication subscription to initialize from backup.

You may be wondering, “Any downsides to this process?“. Well, partial database availability is an Enterprise Only feature. so, yeah, you must be on Enterprise Edition (or Enterprise or Datacenter Edition if on SQL 2008 R2).

Steps to Set Up and Demo Code

I’ve done presentations on this process and written a whitepaper on it. I’m not going to repeat all of the details that I’ve presented elsewhere. I recommend downloading my whitepaper Initializing Replication from Backup and my slide deck and demo code from SQL Saturday #107 in Houston, TX.

Whitepaper: Initializing Replication from Backup
Replication Magic: Initializing from Backup: ReplicationMagic.zip (5.04 MB)