Log shipping restore not happening

Thirunavukkarasu 1 Reputation point
2020-10-15T05:55:59.12+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,255 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. m 4,266 Reputation points
    2020-10-15T07:06:46.067+00:00

    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.


  2. Shashank Singh 6,246 Reputation points
    2020-10-15T10:00:07.937+00:00

    @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.


  3. m 4,266 Reputation points
    2020-10-16T06:12:51.127+00:00

    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.