Log shipping restore not happening

Question

We are using Logshipping SQL Server 2008

Backup & Copy job are running fine.

While restore job executing with below error

Searching through log backup files for first log backup to restore. Secondary DB: 'bill'
2020-10-14 22:05:57.19 Skipped log backup file. Secondary DB: 'bill', File: '\Win-XXXXX\LSBill\bill_20201015050431.trn'
2020-10-14 22:05:57.19 Could not find a log backup file that could be applied to secondary database 'bill'.
2020-10-14 22:05:57.19 The restore operation was successful. Secondary Database: 'bill', Number of log backup files restored: 0
2020-10-14 22:05:57.20 Deleting old log backup files. Primary Database: 'bill'
2020-10-14 22:05:57.20 The restore operation was successful. Secondary ID: '2b7060b1-1462-439d-b069-d9354238c531'
2020-10-14 22:05:57.20 ----- END OF TRANSACTION LOG RESTORE -----

But when I tried to restore manually its working perfectly

No missing LSN, I have validated the same. Restore working fine if i do manually but through LS restore job its skipping wiy=th above msg.

Expert please help us

Answer 1

Hi @Thirunavukkarasu ,

You have a similar issue as this case: sql-server-transaction-log-shipping-does-not-restore-trn-files

So please try similar solution as next:

At the bill DB properties on primary server SQL1, the transaction log replication setting to SQL2 was removed. The wizard also removed the copy and restore jobs at SQL2. The replicated bill DB at SQL2 was removed manually. The transaction log shipping configuration was deactivated at the bill DB on SQL1. All files in the LSBackup and LSRestore folders were deleted. The maintenance plan was deactivated.

Next the transaction log shipping configuration was redone, as described above. Again the target database at SQL2 was initialized by a full backup of the bill DB from SQL1. Finally the secondary server SQL2 was able to restore the .trn files.

More information: log-shipping-in-sql-server-2008

BTW, quote from this doc.: end-of-support-for-sql-server-2008-and-sql-server-2008-r2

...

Customers who use SQL Server 2008 or SQL Server 2008 R2 products and services should upgrade to the latest version of SQL Server on-premises or re-host to Azure SQL Database Managed Instance, a version-less offering that eliminates the need for future upgrades. Or, migrate to Microsoft Azure virtual machines to take advantage of 3 additional years of Critical security updates at no additional charge and modernize when ready.
...

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

@Thirunavukkarasu Could it not be possible that you restored the backups manually when LS job went to do a restore it was not able to find the suitable log backup file to restore. Can you post output of below queries. run 1st on secondary and 2nd one on primary instance

select last_copied_file,last_copied_date,last_restored_file,last_restored_date,last_restored_latency from msdb.dbo.log_shipping_monitor_secondary  

    select last_backup_file, last_backup_datefrom msdb.dbo.log_shipping_monitor_primary  

If restore says it is not able to find the logs to restore, it either means broken LSN chain or means someone already restored it. Or may be Copy job is not copying the log files.

Answer 3

Hi @Thirunavukkarasu ,

The reason of the issue is same as this : sql-server-transaction-log-shipping-does-not-restore-trn-files

The interleaving transaction log backups resulted in holes within the transaction log chain. Therefore the restore job on SQL2 was not able to apply the copied transaction log files.

So please try the solution in my reply:
At the bill DB properties on primary server SQL1, the transaction log replication setting to SQL2 was removed. The wizard also removed the copy and restore jobs at SQL2. The replicated bill DB at SQL2 was removed manually. The transaction log shipping configuration was deactivated at the bill DB on SQL1. All files in the LSBackup and LSRestore folders were deleted. The maintenance plan was deactivated.

Next the transaction log shipping configuration was redone, as described above. Again the target database at SQL2 was initialized by a full backup of the bill DB from SQL1. Finally the secondary server SQL2 was able to restore the .trn files.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.