SQL Server maintains a set of tables in the msdb database, that stores details of all backups and restores that have been performed. Even if you are using a 3rd party backup application, the details are stored if the applications use the SQL Server Virtual Device Interface (VDI) to perform the backups and restores.
The tables that store the details are:
·backupfilegroup (SQL Server 2005 upwards)
·suspect_pages (SQL Server 2005 upwards)
You can find out more about each table from Books Online.
Here's a script to find out the most recent backups for each database.
Another situation I find myself having to query these tables is when my log shipping breaks on the secondary server due to an out-of-sequence log. I could run something like this to find out the last 10 transaction log backups for a particular database:
The physical_device_name tells me which file I will need to continue the transaction log restore sequence, using the first_lsn value as a reference (the value that the secondary server requires to continue restoring the transaciton logs). The user_name tells me who I need to have some serious words with!
Or say I need to restore an entire sequence of transaction logs created after 10-Jan-2008, up to a point in time on 16-Jan-2008 3:30 AM. I could use the following query to display the files I need to restore in sequence:
The family_sequence_number tells me how may files are containined in each backup set, and position tells me which backup set in a file I need to use, when the backup file contains multiple backup sets.
Now, these tables can grow pretty large over time. SQL Server provides the sp_delete_backuphistory stored procedure to delete historical data. E.g.
deletes all details of backup and restore processes created before January 1 2005. Note that this stored procedure exists in the msdb database, not the master database.
In SQL Server 2005 and newer, there is also a sp_delete_database_backuphistory stored procedure to delete historical data for a specific database e.g.