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

One possible solution that seems promising to me is to use the "Last Database Log Backup" and "Last Database Backup" fields in the General tab of the "Database Properties" window. Does anyone know where this data is stored or a command that will return the value for a database? Unfortunately I can't run a trace on any servers with log shipped databases here due to permission issues. Also, are these dates the "data as of" date of the backups and logs or just the time at which the backups and logs were applied on the server? Obviously the latter wouldn't help me much.

Answer 2

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.