0

Hi have a big database (~30GB) that I need to replicate to two server on two different locations. I enabled replication and enabled "allow initialize from backup" and did the following steps:

  1. backup the database on master server
  2. copy backup file to server1.
  3. Restore the database on server1
  4. Add the subscription on the master server.

Replication from master server to server1 is working.

  1. took a fresh backup on master server.
  2. copy and restore on server2
  3. Add subscriber on server2 (using sp_addsubscription)

Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account. Job 'XXX-181' started successfully. Msg 21397, Level 16, State 1, Procedure sp_MSsetupnosyncsubwithlsnatdist, Line 248 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. The Subscriber was dropped.

What an I doing wrong? do I must use the same backup file for both servers? Thanks

user2993562
  • 51
  • 1
  • 7

1 Answers1

0

the lsn for the backup must be in the distribution database as part of xact_seqno. You may have passed the retention period, which means the lsn in the backup is not longer in the distribution database.

Best thing to do is to stop the distribution cleanup agent, drop and recreate the problem subscription, backup the database, recreate the subscription and then try again.

HilaryCotter
  • 109
  • 1
  • This fixed the issue for me. If anyone else has this problem, I didn't have to drop and recreate the subscription - I just had to take a fresh backup and transfer again. I think the distribution cleanup job ran overnight while I was transferring the original backup to the subscriber. – Jim G. Jun 17 '15 at 21:05