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.

Replication Wizards do not have options for creating subscription using “allow initialization from backup”. Instead use SQL commands when creating the subscription supplying the database backup file location.

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

This creates the subscription and the Distribution agent should start replication commands starting at the LSN location as indicated in the backup header information.

Possible errors when the subscriber is initialized from backups

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

min_autonosync_lsn will be the same as the syspublications.min_autonosync_lsn

During the execution of sp_addsubscription, we perform validation in sp_MSaddautonosyncsubscription procedure before we create the subscription.

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

Purged Backup Watermark

When sp_addsubscrption with @backupdevicename=’backup device’, is executed, the “RESTORE HEADERONLY” is run against the backup and returning LASTLSN of the latest backup set.  If the backup set contains multiple backups. This LASTLSN is used as watermark for Distribution agent for when retrieving new transactions from msrepl_transactions.  If the Backup\Restore processes took too long, its possible the Distribution cleanup job has already purged the transaction watermark.  You can use query below to validate LSN watermark.

Execute "RESTORE HEADERONLY FROM DISK" on the backup set returning the lastLSN. Using the following SQL commands, convert binary lsn into LSN format stored in the Distribution database.

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)

If the LSN watermark has already been purged from the Distribution database you’ll need to drop and add the Subscriber using one of these options:

• Disable the Distribution Cleanup Agent
• Increase the Maximum Cleanup Retention Period
• With Publisher in Full Recovery, first apply Full backup\restore, then create subscriber using Transaction Log Backup\Restore to catch up Subscriber.

Single Backup Set

If the backup set contains multiple backup sets, the sp_addsubscrption logic select the first backup set and not the last backup set.  This may return an older watermark.  For example, if the Backup Set contains Full and TLOG backup, the watermark for Distribution Agent could use TLOG LSN, however, if backup set contains both, the add subscription command will only return the older FULL watermark LSN.

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