Init Replication From Backup

Published On: 2012-03-01By:

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: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.


Contact the Author | Contact DCAC

3 responses to “Init Replication From Backup”

  1. Nsrao says:

    Excellent technique… Wondering whether it works for a single article too ???


  2. Mrdenny says:

    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.

  3. SQLSoldier says:

    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.


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link