2

Recently I configured Log shipping between 2 different servers (all SQL Server 2008 R2), from primary to secondary (2 instances). Log shipping configured successfully and databases on secondary servers are Standby/Read only mode. I mean from primary to 2 secondary servers I added for Log shipping.

Today I observed Log backup is happening on primary but it's not restoring into secondary. Is the Full Backup on primary breaking Log shipping, or are there any other reasons for breaking Log shipping?

@ Kris Gruttemeyer, Transactional Log shipping status on secondary server seems like below:

enter image description here

Error Message for Restoring data in View Job History while Running Restore Job on Log shipping server (Secondary): "Could not find a log backup file that could be applied to secondary database" and “The restore operation completed with errors. Secondary ID: '807202d2-18a6-4236-85ad-ae3a51fba2eb'”

There is no answer for previous question as well: What does 'Could not find a log backup file that could be applied' means on log shipping?. Please help me on this?

  • 2
    The copy job is failing as per the image can you check that. Can you also confirm that backup,copy job is running successfully ? – Shanky Jun 14 '17 at 17:06
  • 1
    Odds are if the copy job has been failing for that long, you may need to re-initialize the whole thing because those backup files were probably removed by the backup retention configuration setting when you initially configured it. Judging by that picture, log shipping has been non-operational for almost a month. – Kris Gruttemeyer Jun 14 '17 at 17:11
  • Copy and Backup jobs are stopped working. So I need to do Re-config the Log Shipping again? – CR241 Jun 14 '17 at 18:08
  • What do you mean by stopped working? Can you read the history and post its content here for both the jobs – Shanky Jun 14 '17 at 18:10
  • 1
    Odds are it's because it's TOO far out of sync. Default backup file retention is 7 days I think so if you go beyond that time frame, the log backups no longer exist. Your best bet it to go scorched earth and re-initialize the log shipping secondary. – Kris Gruttemeyer Jun 14 '17 at 20:43
3

FULL backups do not break log shipping. FULL backups simply reset your differential base for all subsequent DIFF backups until the next FULL occurs.

Log shipping occurs in 3 phases:

1- Log backup on the primary

2- Copy log file to destination server (or network share)

3- Restore Log to secondary

If using default settings, these each occur in 15 minute intervals. This means that it can take up to 45 minutes for a log file taken on the primary to be successfully applied to the secondary.

An easy way to verify that things are in sync is use the 'Transaction Log Shipping Status Report' on the secondary server by right clicking the server name in SSMS and navigating to the following:

enter image description here

EDIT: Here's where to look:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.