Check if database is restored to the most recent log file

Folks,

I am running a copy of our production database as a backend for an Instance of SQL Server Reporting Services. The copy is being updated off transaction logs which are taken frequently (hourly) for backup purposes using RESTORE DATABASE WITH NORECOVERY / WITH STANDBY. I obviously cannot use the built-in transaction log shipping facilities as the database is already being BACKUP LOGged and trying to use log shipping would break both mechanisms' log chains.

But I need some way to monitor if my copy is up-to-date, ideally without changing the original database's schema or data. My first approach would contain retrieving the most recent log backup's last LSN using RESTORE HEADERONLY FROM DISK and comparing it to the LSN of my copy instance. But I am having difficulties finding an LSN which would be able to match the result from RESTORE HEADERONLY - what I see in sys.database_files seems to be a different thing entirely.

Where would I find matchable LSNs?

You should be able to use the restorehistory table on the server to which you are restoring to get the most recently restored LSN.

SELECT TOP 1 b.type, b.last_lsn
 FROM msdb.dbo.restorehistory a
 INNER JOIN msdb.dbo.backupset b 
     ON a.backup_set_id = b.backup_set_id
 WHERE a.destination_database_name = 'YourDatabaseName'
 ORDER BY restore_date DESC

This was posted in a discussion of log files some years ago at: http://www.sqlbackuprestore.com/logsequencenumbers.htm

EDIT: As a further option, if you are using Enterprise Edition you can use database snapshots for reporting. The database in this case remains in restore mode and the snapshot(s) read only. If this is your environment, you could indeed use log shipping to keep your Reporting Server data up to date.

StackExchange Database Administrators Q#65890, answer score: 3