Log Shipping Error “Could not find a log backup file that could be applied to secondary database”

A simple explanation on how log shipping works could be “Backup-Transfer-Restore”. Transaction log will backup on the primary database and transfer the log backup file to the secondary database server, eventually restore it to the database. Thus, any changes on the primary database will reflect on the secondary database.

How to trouble shoot when the log shipping failed and the below error message found in the job history?

Could not find a log backup file that could be applied to secondary database '<DATABASE_NAME>'.

This means the current log backup file which being restored is too recent. A backup from a prior time need to be applied first. Since the log being backed up in sequence on the primary server, the log need to restore according to the sequence as well on the secondary database.

Use the following query to check when is the last log backup file that was copied:

SELECT * FROM [msdb].[dbo].[log_shipping_secondary]

And, also check what was the last log backup file was restore.

SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

Sometime larger transaction log file may takes longer time to copy over to the secondary server while the newer file already copied over, thus it looks unsorted on the secondary database. So wait for the big file to be copied and restart the log shipping job again. Second case, you may need to check the retention time on backup job that run on primary server.

If you see the file on primary server that was not yet copied, you may need to manually copy that over and then start the log shipping again. If the file is not there, you may need to reconfigure the log shipping process again. You can refer to this site on how to configure log shipping for your database.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.