Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am log shipping between two SQL Server 2008 servers. This is our own log shipping and not the built in (we have 500+ databases and it is easier this way). I want simple monitoring to make sure the fail-over site has the latest transaction logs.

How can I tell the latest transaction log that was restored to a database?

Can I tell the date/time that the log was backed up from the source server?

share|improve this question
up vote 1 down vote accepted

The engine keeps a history of all backup operations in MSDB tables. See Viewing Information About Backups. Eg. the backupset table will contain

a row for each backup set. A backup set contains the backup from a single, successful backup operation.

You can get the LSN and date of the last backup of each DB from there. Then you can look on your destination MSDBs and inspect the restorehistory table which contains one row for each restore operation and you can see when was last restore applied.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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