I have some concerns regarding the restore job on secondary server in log shipping in Microsoft SQL Server 2016 for below scenario :

I have two servers, primary and secondary. The backup, copy and restore job frequency is 5 mins. Suppose some disaster happened and primary server is crashed and now we don't have any access to primary server.

On secondary server we have copied 3 log files through log shipping job which are not restored yet.

At present 1st log file is restoring and rest two files still not restored. since our primary server is crashed so we have to make the secondary server Up and make it as primary server.

So before making secondary server as primary How do I ensure that all 3 log files are restored or not ??

  • 1
    Please, add the RDBMS you're talking about. It might be relevant to answer your question.
    – Ronaldo
    Jul 3, 2021 at 17:01

1 Answer 1



Using TSQL get details of last restored transaction log Backup (Subscriber Side):

SELECT b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn, a.*
FROM msdb..restorehistory a
INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id
WHERE a.destination_database_name = ?
ORDER BY restore_date DESC

Here, First LSN is an LSN reference of last-second generated database backup and LAST LSN is an lsn reference of last generated database backup.

Using SQL Server Management Studio, on your secondary server:

For each secondary database, the status and name of the current server instance (as the secondary server), along with the secondary database name.

The report displays the status of the copy and restore jobs at the secondary server.

The report also contains a row for the corresponding primary server. If the configuration uses a monitor server and the stored procedure can connect to the monitor, this row displays the status of the most recent log backup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.