How to query SQL Server backup and restoration history

SQL Server maintains a series of tables in MSDB data to store all the backup and restoration details. Even if you are using a third-party backup application, as long as the application uses the virtual device interface (VDI) of SQL Server for backup and restoration, the execution details are still stored in this series of tables.

Tables that store details include:

• Backupset
• Backupfile
• Backupfilegroup (SQL Server 2005 upwards)
• Backupmediaset
• Backupmediafamily
• Restorehistory
• Restorefile
• Restorefilegroup
• Logmarkhistory
• Suspect_pages (SQL Server 2005 upwards)

You can find the specific descriptions of these tables in books online.

The following script can help you find the recent backup information of each database:

Select B. Name, A. type, max (A. backup_finish_date) lastbackup
From MSDB .. backupset
Inner join Master... sysdatabases B on A. database_name collate database_default = B. Name collate database_default
Group by B. Name, A. Type
Order by B. Name, A. Type

Specifies the last 20 transaction log backup information of the database:

Select top 20 B. physical_device_name, A. backup_start_date, A. first_lsn, A. user_name from MSDB... backupset
Inner join MSDB .. backupmediafamily B on A. media_set_id = B. media_set_id
Where a. type = 'l'
Order by A. backup_finish_date DESC

Transaction Log backup information for a specified period of time:

Select B. physical_device_name, A. backup_set_id, B. family_sequence_number, A. Position, A. backup_start_date, A. backup_finish_date
From MSDB .. backupset
Inner join MSDB .. backupmediafamily B on A. media_set_id = B. media_set_id
Where a. database_name = 'adventureworks'
And a. type = 'l'
And a. backup_start_date> '10-Jan-2007'
And a. backup_finish_date <'16-Jan-2009'
Order by A. backup_start_date, B. family_sequence_number

Two stored procedures for deleting backup logs:

Exec MSDB .. sp_delete_backuphistory '1-Jan-2005'

Exec MSDB .. sp_delete_database_backuphistory 'adventureworks'