0 Find out Backup & Restore History from MSDB

We can find out Backup & Restore History from below 2 tables in MSDB.

• backupset: Backup History
• restorehistory: Restore History.

Below Query list all the database backup history

1. SELECT a.name, b.type, b.backup_finish_date AS LastSuccessfulBackup,b.backup_finish_date
2. FROM master..sysdatabases a
3. LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
4. ORDER BY a.name, b.type

D means the database backup. L is the transaction log bakcup

Restore history is much more straight forward

1. select * from restorehistory

Reference

Viewing Information About Backups http://msdn.microsoft.com/en-us/library/ms188653.aspx

Backup/Restore history  http://www.sqlbackuprestore.com/backuprestorehistorytables.htm