How to Ensure all Log backups are restored on secondary server in log shipping

Question

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 ??

Answer 1

Reference:

• log_shipping_monitor_secondary (Transact-SQL)
• sp_help_log_shipping_monitor (Transact-SQL)
• Monitor Transaction Log shipping using T-SQL and SSMS by Jignesh Raiyani
• View the Log Shipping Report (SQL Server Management Studio)

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.