Problems initializing a subscription from a backup

• Roust_m

  SSCoach

  Points: 17410

  More actions

  February 22, 2016 at 10:13 pm

  #311601

  Hi,

  I am trying to initialize a subscription from a backup.

  I am getting this error:

  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.

  allow_initialize_from_backup for the publication is set to 1.

  This query:

  select min_autonosync_lsn,* from syspublications where name = 'XYZ'

  returns:

  0x0000027B00001BAF006D

  This query:

  select min_autonosync_lsn,* from distribution.dbo.mspublications where publication ='XYZ'

  also returns:

  0x0000027B00001BAF006D

  RESTORE HEADERONLY FROM DISK = 'c:\temp\xyz.bak'

  returns:

  635000000059800001

  I convert it into binary with this query:

  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)

  and get:

  0x0000027B000002560001

  I repeat the process of backup/restore and init and get these numbers:

  0x0000027B00002169006C

  0x0000027B00002169006C

  635000000059800001

  0x0000027B000002560001

  It looks like min_autonosync_lsn keeps changing every few minutes even though allow_initialize_from_backup is set to 1 all the time.

  This happens only to one database out of 7 replicated on the server. For other 6 the same process of initializing the subscription from a backup works fine.

  I used this link to troubleshoot:

  http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx

  Any ideas?

  Thanks

• r.dragoi-1095738

  SSCrazy

  Points: 2022

  More actions

  February 23, 2016 at 1:45 am

  #1860297

  See if this helps : http://blogs.technet.com/b/warreny/archive/2010/12/27/sql-server-transactional-replication-resynchronization.aspx

  -- @distribution Cleanup Agent

• umaShankar godi

  SSC Veteran

  Points: 213

  More actions

  December 15, 2023 at 12:43 pm

  #4346562

  we have encountered this issue recently . I guess it is due to.. while in command to set up replication using backup file ..sys.sp_addsubscription @backupdevicename='xxx.bak', from backup file mentioned it gets the lastlsn using restoreheaderonly command . if txns after the lsn is not available in the active log, it fails to get them into distribution database. So remedy is stop the backups(log/diffs) in source database after the backup file mentioned in  sp_addsubscription and use the command . I took help of below article to get understanding.

  https://www.experts-exchange.com/questions/26453211/Error-while-restoring-backup-while-setting-up-transactional-replication.html

   

   

  
  UmaShankar