venk_r
asked on
Error while restoring backup while setting up transactional replication
Msg 21397, Level 16, State 1, Procedure sp_MSaddautonosyncsubscrip tion, Line 279
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.
Thanks
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.
Thanks
Which statement or operation have you run when you get this message (please provide the full statement)?
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_back up, min_autonosync_lsn,* from syspublications
allow_initialize_from_back up = 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.mspublica tions
min_autonosync_lsn will be the same as the syspublications.min_autono sync_lsn
During the execution of sp_addsubscription, we perform validation in sp_MSaddautonosyncsubscrip tion procedure before we create the subscription.
--on distribution database
select subscription_seqno,publish er_seqno,s s_cplt_seq no,* from distribution.dbo.mssubscri ptions
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_MSextractlastlsnfrombac kup proc. This proc is executed inside sp_MSaddautonosyncsubscrip tion procedure.
RESTORE HEADERONLY FROM DISK on the backup set and get the lastLSN from the latest backup. Use the following SQL to get the binary form of the lsn.
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(@nume riclsn / 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.
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_back
allow_initialize_from_back
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.mspublica
min_autonosync_lsn will be the same as the syspublications.min_autono
During the execution of sp_addsubscription, we perform validation in sp_MSaddautonosyncsubscrip
--on distribution database
select subscription_seqno,publish
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_MSextractlastlsnfrombac
RESTORE HEADERONLY FROM DISK on the backup set and get the lastLSN from the latest backup. Use the following SQL to get the binary form of the lsn.
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(@nume
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.
I'll suggest you to reinitialize the replication with new snapshot.
ASKER
I cannot reintialize the snapshot as the data is huge and it will take almost 2 days to complete.The only I got is to restore the backup on the subscriber.Meanwhile I have tried restoring differential backups followed by full still no luck.Just to let you the test system(publisher) as got no active transations.So there is no chance of LSN going out of sync.
Thanks
Thanks
Well, if you won't create new snapshot then restore subscriber database should work as well.
Good luck
Good luck
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks