Monitoring Log Shipped Databases

Question

I need a consistent way to monitor databases that are read-only log shipped copies of production databases. In the past I have relied on the following methods:

1. Set the job that restores logs to the database kick off another job as its last step.
2. Set the job that restores logs to the database to insert a record in a control table as its last step.
3. Query the msdb database to check the status of the job that restores logs to the database.
4. Query a control table inside the database itself that gets a value immediately before transaction logs are backed up.
5. Query MAX values from tables inside the database to see if it has recent changes.

Although the above methods work, they can't be implemented for every log shipped database that I query for various reasons. What is the best method for monitoring the "data as of" date for a log shipped database?

Answer 1

You have all the information you need in the Backup and Restore tables:

• Look in restorehistory to find the most recent backup applied to the database of interest.
• Look on the source backupset table to find the date the backup discovered above was taken.