Solved

Error while restoring backup while setting up transactional replication

Posted on 2010-09-05
7
1,464 Views
Last Modified: 2012-05-10
Msg 21397, Level 16, State 1, Procedure sp_MSaddautonosyncsubscription, 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
0
Comment
Question by:venk_r
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

by:Máté Farkas
ID: 33609682
Which statement or operation have you run when you get this message (please provide the full statement)?
0
 
LVL 8

Expert Comment

by:avirups
ID: 33609936
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

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.

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(@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.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 33609949
I'll suggest you to reinitialize the replication with new snapshot.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Author Comment

by:venk_r
ID: 33610723
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
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 33610829
Well, if you won't create new snapshot then restore subscriber database should work as well.

Good luck
0
 
LVL 8

Accepted Solution

by:
avirups earned 500 total points
ID: 33615143
Did you get a chance to try the solution posted above? Let me know if that did not work.
0
 
LVL 8

Author Closing Comment

by:venk_r
ID: 33757919
thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Auditing in Azure SQL Database 3 38
Caste datetime 2 51
Service Statictic 11 30
Columnstore Indexes - real-time operational analytics 1 8
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now