Posted by: Denny Cherry
Replication, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2
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.