SQL Server with Mr. Denny

Mar 1 2012   2:00PM GMT

Init Replication From Backup

Denny Cherry Denny Cherry Profile: Denny Cherry


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
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:\Backup\YourDatabase.bak'

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


3  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • Nsrao
    Excellent technique... Wondering whether it works for a single article too ??? Thanks, nsrao
    0 pointsBadges:
  • Denny Cherry
    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.
    68,390 pointsBadges:
  • SQLSoldier
    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.
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: