What is Log shipping?
SQL Server Log shipping is an automatic process that sends transaction log backups from a primary database server instance to secondary databases server on secondary server instances. The transaction log backups are applied to secondary databases.
There will be three jobs continuously running namely backup, copy and restoration jobs. Now we will discuss restoration job failures.
Issue:
In the log shipping restoration job may fail due to several issues like permission issue and LSN mismatch in other words order of transaction log backup files are not restoring, it would be due to manual backups
Error:
*** Error: The file ‘D:\LS_copy\Advk_******.trn’ is too recent to apply to the secondary database ‘Advk’.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: The log in this backup set begins at LSN 53000003154500001, which is too recent to apply to the database. An earlier log backup that includes LSN 53000003153900001 can be
Solution:
- Disable all jobs in log shipping which are backup job, copy job and restore job
- Should check transaction log backup time which is failing to restore.
- Should check any full backup happened after that time (after T log backup which is failed to restore)
- If no full backup happened after log backup, we have take differential backup on primary server.
- Restore that differential backup on secondary server database with no recovery
- We can delete T log backups which occurred before differential backup.
- Then enable jobs in log shipping which are backup, copy and restoration.
- If full backup happened after T log failing time differential backup will not work
- We should restore full backup, it will be like newly configuring log shipping.
Conclusion:
When taking manual T log backups on database which is configured for log shipping keep safe until restore them manually. Always try to avoid manual transaction log backups to keep safe log shipping to fulfil purpose of HADR setup
