3

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?

Improve this question
13
  • 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
    Commented Jun 14, 2017 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
    Commented Jun 14, 2017 at 17:11
  • Copy and Backup jobs are stopped working. So I need to do Re-config the Log Shipping again?
    – CR241
    Commented Jun 14, 2017 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
    Commented Jun 14, 2017 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
    Commented Jun 14, 2017 at 20:43

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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