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:
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?
Best Answer
FULL
backups do not break log shipping.FULL
backups simply reset your differential base for all subsequentDIFF
backups until the nextFULL
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:
EDIT: Here's where to look: