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.

[sql]BACKUP DATABASE YourDatabase TO DISK=’E:BackupYourDatabase.bak’ WITH FORMAT, STATS=10
USE YourDatabase
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:BackupYourDatabase.bak’

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



3 Responses

  1. Yes this can be used for a single article as well. You would just need to use something like LiteSpeed for SQL Server to do the restore of the single table from the backup and it’ll work just fine.

  2. A few issues here.

    1. Initialize from backup was introduced in SQL Server 2005. This option is not available in SQL Server 2000.

    2. You can’t simply add a subscription initialized from backup to a publication that was set up the normal way. You have to set the publication to allow subscription from backup before you create the backup you will be initializing from.

    3. You can’t use a LiteSpeed backup as the initialize point for the initilize from backup. SQL has to be able to read the backup file natively. You can use LiteSpeed to do an initial restore and then initialize from a native log backup.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?