Init From Backup

One of the biggest hurdles to using replication on large databases is doing the initial snapshot. If you're new to replication (and we're focusing on transactional replication in this article) a snapshot is the process of putting the tables and related data on the subscriber. A vanilla snapshot consists of scripting out the tables and indexes, BCP'ing out the data, then applying the scripts to the subscriber, and finally BCP'ing the data in. It's often faster than expected based on the database size because it's only the data being moved, not the indexes themselves. Speed is a function of available disk IO on both machines as well as the network. Doing a snapshot of a terabyte database across a WAN can be an exercise in slow.

Prior to SQL 2005 there weren't many great alternatives if you were set on using replication. One way was to quiesce the system; deny users the ability to change anything, then run a full backup, copy it to a portable drive, drive or overnight it to the subscriber location, load it, and then start replication by configuring it to know that the subscriber already had the data and finally let the users resume work. Worked okay if you could do this overnight or weekend. SQL 2000 introduced the concept of a concurrent snapshot - basically a non blocking snapshot. This let us run the snapshot with minimal impact on users. It didn't change the time required to post the data to the subscriber though. SQL 2005 now gives us the option to initialize a subscriber from backup without much  pain. It's not supported via the replication dialogs in SSMS, but it's relatively easy to do and I'm going to demonstrate that today. This article assumes you already know how to set up replication and build a publication.

After creating a publication, right click it and select properties. Change the allow initialization from backup files setting to True, then close the dialog.

The next step is to run a full backup. I'm using Adventureworks and I'm replicating just the Person.Address table for this exercise.

backup database AdventureWorks to disk='c:\aworks.bak'

Then, restore the file to the subscriber server (in this case I'm going to restore to the same machine, my laptop):

restore database AdventureWorksSubscriber from disk='c:\aworks.bak' with
move 'AdventureWorks_data' to 'c:\aworkssub.mdf',
move 'AdventureWorks_log' to 'c:\aworkssub.ldf'

Now we need to create the subscription on the publisher and for it to use the restored copy, we have to do this with TSQL:

use adventureworks
go
sp_addsubscription 
	@publication ='Test', --your pub name here
	@subscriber='earlgrey', --subscriber server name
	@destination_db='AdventureWorksSubscriber',
	@sync_type = 'initialize with backup',
	@backupdevicetype = 'disk',
	@backupdevicename = 'c:\aworks.bak'
go

We can see the subscription has been added in SSMS:

To prove it's working we'll update one row on the publisher, wait a few seconds, and then verify on the subscriber:

update person.address set addressline2='TEST UPDATE' where addressid=1
use adventureworkssubscriber
go
select * from person.address where addressid=1

The only downside to this technique is that the subscriber has a complete copy of the database. In our case we only wanted to replicate one table. If we're concerned about security, or the amount of space used on the subscriber, we can just start dropping tables and other objects after we get everything going. It's easy enough to use and maybe they'll support it better in a service pack or SQL 2008.

I blog once a week or so at http://blogs.sqlservercentral.com/blogs/andy_warren/default.aspx about SQLServer, SQL user groups, and related topics. I hope you'll visit and comment occasionally!