Init Replication From Backup

One of the great features with SQL Replication is the ability to initialize a subscription from backup instead of from a snapshot.  The official use for this is to take a database backup and restore it to a subscriber then replicate any additional changes to the backup.

However this technique can be used to get replication back up and running after moving the publisher to another SQL Server.  Simply setup the publication just like normal, then backup the database and add the subscription using the “initialize with backup” value for the @sync_type parameter as shown in the sample code below.

If you were going to actually initialize a new subscription using a backup like the feature was written to be used, then after the backup has happened restore the database to the subscriber under the correct database name.

BACKUP DATABASE YourDatabase TO DISK='E:\Backup\YourDatabase.bak' WITH FORMAT, STATS=10
GO
USE YourDatabase
GO
EXEC sp_addsubscription @publication = N'YourDatabase Publication', @subscriber=N'ReportServer', @destination_db = N'ReportingDatabase', @article='all',
@sync_type='initialize with backup', @backupdevicetype='disk', @backupdevicename='e:\Backup\YourDatabase.bak'
GO

This technique should work on all versions of SQL Server from SQL Server 2000 up through SQL Server 2012 without issue.

Denny