Deep Dive on Initialize from Backup for Transactional Replication

Sateesh Yele
Microsoft SQL Server Support

How to initialize the subscriber from 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

   
    

5. This should create the subscription and the Distribution agent should be replicating the commands successfully.

Troubleshooting replication issues when the subscriber is initialized from the backup.

When the subscriber is initialized from backup, there can be so many things that can go wrong. Below are some of the error messages that might encounter during replication setup.

Msg 21408

Cannot create the subscription. You must specify a value of "Active" or "Subscribed" for the @status parameter. This is because the value specified for the @sync_type parameter is "initialize with backup" or "replication support only".

Msg 18786

The specified publication does not allow subscriptions to be initialized from a backup. To allow initialization from a backup, use sp_changepublication: set 'allow_initialize_from_backup' to 'true'.

Msg 21407

Cannot create the subscription. If you specify a value of "initialize with backup" for the @sync_type parameter, you must subscribe to all articles in the publication by specifying a value of "all" for the @article parameter.

Msg 21399

The transactions required for synchronizing the subscription with the specified log sequence number (LSN) are unavailable at the Distributor. Specify a higher LSN.

Msg 21397

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.

Quick Checks:

-- on the publisher database

select allow_initialize_from_backup, min_autonosync_lsn,* from syspublications

allow_initialize_from_backup  = 1 
min_autonosync_lsn = the lsn from which the commands should be applied at the subscriber

--on distribution database

select min_autonosync_lsn,* from distribution.dbo.mspublications

--on distribution database

select subscription_seqno,publisher_seqno,ss_cplt_seqno,* from distribution.dbo.mssubscriptions

min_autonosync_lsn should be always less than the subscription_seqno

When we execute sp_addsubscrption with @backupdevicename='backup device', we are executing the “RESTORE HEADERONLY” on the backup and getting the LASTLSN of the latest backup if the backup set contains multiple backups. All this logic is handled in sp_MSextractlastlsnfrombackup proc. This proc is executed inside sp_MSaddautonosyncsubscription procedure.

declare @numericlsn numeric(25,0)

declare @high4bytelsncomponent bigint,

@mid4bytelsncomponent bigint,

@low2bytelsncomponent int

--set the lsn here

set @numericlsn = 93000000070800001

select @high4bytelsncomponent = convert(bigint, floor(@numericlsn / 1000000000000000))

select @numericlsn = @numericlsn - convert(numeric(25,0), @high4bytelsncomponent) * 1000000000000000

select @mid4bytelsncomponent = convert(bigint,floor(@numericlsn / 100000))

select @numericlsn = @numericlsn - convert(numeric(25,0), @mid4bytelsncomponent) * 100000

select @low2bytelsncomponent = convert(int, @numericlsn)

SELECT  convert(binary(4), @high4bytelsncomponent) + convert(binary(4), @mid4bytelsncomponent) + convert(binary(2), @low2bytelsncomponent)

This is the lsn from which the Distribution agent will pick up the commands from msrepl_transactions and apply at the subscriber.

Potential Problems

If the backup set contains multiple backup sets, the sp_addsubscrption logic select the first backup set and not the most recent backup set.  As result, the Subscriber may not have the most recent copy of the Publication database.  After all, reason we we’re doing this method is to reduce the data transfer time to the Subscriber.  We didn’t count on the backup set having multiple copies of the Published database.  Kinds of defeats trying to “save time” using Backups.

Solution: When using “allow initialization from backup files” always create the Publisher backup to a NEW backup (*.bak) file.

--posted by Chris Skorlinski