Initialize Subscriber from Differential Backup

Share this Post

My colleague Lubín Hidalgo Carro, Microsoft SQL Support Engineer, was helping a customer initialize the Transactional Replication subscriber via backup\restore method as documented here and on docs.microsoft.

https://blogs.msdn.microsoft.com/repltalk/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication/

https://docs.microsoft.com/en-us/sql/relational-databases/replication/initialize-a-transactional-subscription-from-a-backup

The main steps shown below are to create the publication, change allow init from backup, then take a full backup.

  1. Create the publication using user interface Replication Wizard.
  2. Under subscription options for the publication, set “allow initialization from backup files” to true
  3. Create a new full backup of the publisher database. If you have existing full backup of the publisher database, you can still use that backup set but we have take a new log backup or differential backup of the publisher database and restore at the subscriber.
  4. Replication UI does not allow the option to create the subscription to allow initialization from back. We have to TSQL when creating the subscription.
    exec sp_addsubscription @publication = N’Repl2000′, …..
    @sync_type = N’initialize with backup’,

    @backupdevicetype=‘Disk’,
    @backupdevicename=‘C:\Repl2000_RestoreThis.bak’–this is the last backup used to restore on the subscriber that was taken after the publication was created
    go

    exec sp_addpushsubscription_agent …….
    go

His customer called when replication failed with error shown below.

The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup. The subscription to publication ‘Transactional’ has expired or does not exist.

Turns out our customer took the backup before the publication was created, step 0.5 if you will. User transactions created between when backup was taken, and Publication created (Log Reader running) would not be cached in the Distribution database resulting in missing metadata. What to do?

Catch up Subscriber

Lubin’s solution was to drop the subscription, “catch up” the subscriber using a differential backup, then add back the subscriber referencing the differential backup in @backupdevicename parameter for sp_addsubscription.  Another way to “catch up” the subscriber would be via transaction log restores.

To learn more, you can walk through his example using steps below

Walk Through – Initialize Subscriber via Differential Backup\Restore

The walkthrough below is based on published database called ReplDB with one table called ReplTable.

1. Full backup (to be publisher) and restore on target (to be subscriber)

2. Transaction #1 on source (to be publisher)

3. @sp_addpublication

4. Transaction #2 on source (to be publisher)

5. Differential backup and restore on target (if backup\restore takes > 24 hours, disable Distribution Cleanup jobs)

6. @sp_addsubscription @device = differential backup

7. Transaction #3 on source (to be publisher)

8. Verify Log Reader and Distribution Agents are running
9. Insert Tracer Token and confirm if all transactions were delivered.

Written by
Lubin Hidalgo Carro
Microsoft SQL Server Support Services
Posted by
Chris Skorlinski
Microsoft SQL Server Escalation Services


Share this Post

About: ReplTalk


Leave a Reply

Your email address will not be published. Required fields are marked *

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