Backup and Restore History details

Address: http://www.sqlbackuprestore.com/backuprestorehistorytables.htm

SQL Server maintains a set of tables inMSDBDatabase, that stores details of all backups and restores that have been stored med. 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:

·

Backupset

·

Backupfile

·

Backupfilegroup (SQL Server 2005 upwards)

·

Backupmediaset

·

Backupmediafamily

·

Restorehistory

·

Restorefile

·

Restorefilegroup

·

Logmarkhistory

·

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.

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

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 cocould run something like this to find out the last 10 transaction log backups for a particle 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

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 serous words!

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. I cocould use the following query to display the files I need to restore in sequence:

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

The family_sequence_number tells me how may files are contained 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 providesSp_delete_backuphistoryStored Procedure to delete historical data. E. g.

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

Deletes all details of Backup and Restore processes created before January 1 2005. Note that this stored procedure exists inMSDBDatabase, notMasterDatabase.

Note:In SQL Server 2000, the performance of this stored procedure is pretty dismal When deleting large number of records, due to the use of cursors. here's a stored procedure you can try, adapted fromForum postOn sqlteam.com, that does not use cursors: Create proc sp_delete_backuphistory_alt (@ Daystoretain INT) As Set nocount on Declare @ interrno int Declare @ dtcutoff datetime Begin transaction deletebackuphistory Delete from MSDB .. restorefile From MSDB .. restorefile RF Inner join MSDB .. restorehistory RH on RF. restore_history_id = Rh. restore_history_id Inner join MSDB... backupset BS on RH. backup_set_id = BS. backup_set_id Where BS. backup_finish_date <(getdate ()-@ daystoretain) Select @ interrno = @ Error If @ interrno <> 0 goto abortsql Delete from MSDB .. restorefilegroup From MSDB .. restorefilegroup rfg Inner join MSDB .. restorehistory RH on rfg. restore_history_id = Rh. restore_history_id Inner join MSDB... backupset BS on RH. backup_set_id = BS. backup_set_id Where BS. backup_finish_date <(getdate ()-@ daystoretain) Select @ interrno = @ Error If @ interrno <> 0 goto abortsql Delete from MSDB... restorehistory From MSDB .. restorehistory RH Inner join MSDB... backupset BS on RH. backup_set_id = BS. backup_set_id Where BS. backup_finish_date <(getdate ()-@ daystoretain) Select @ interrno = @ Error If @ interrno <> 0 goto abortsql Select media_set_id, backup_finish_date Into # temp From MSDB .. backupset BS Where backup_finish_date <(getdate ()-@ daystoretain) And not exists (Select bs2.media _ set_id from MSDB... backupset bs2 Where BS. media_set_id = bs2.media _ set_id and bs2.backup _ finish_date> @ dtcutoff) Select @ interrno = @ Error If @ interrno <> 0 goto abortsql Delete from MSDB... backupfile From MSDB .. backupfile BF Inner join MSDB... backupset BS on BF. backup_set_id = BS. backup_set_id Inner join # temp t on BS. media_set_id = T. media_set_id Select @ interrno = @ Error If @ interrno <> 0 goto abortsql Delete from MSDB... backupset From MSDB .. backupset BS Inner join # temp t on BS. media_set_id = T. media_set_id Select @ interrno = @ Error If @ interrno <> 0 goto abortsql Delete from MSDB .. backupmediafamily From MSDB .. backupmediafamily BMF Inner join MSDB... backupmediaset BMS on BMF. media_set_id = BMS. media_set_id Inner join # temp t on BMS. media_set_id = T. media_set_id Select @ interrno = @ Error If @ interrno <> 0 goto abortsql Delete from MSDB .. backupmediaset From MSDB .. backupmediaset BMS Inner join # temp t on BMS. media_set_id = T. media_set_id Select @ interrno = @ Error If @ interrno <> 0 goto abortsql Abortsql: If @ interrno <> 0 Begin Rollback End Else Begin Commit transaction sqbdeletebackuphistory End Drop table # temp Exitsql: Set nocount off Another suggestion to speed up the deletion is to create an index on the media_set_id column in The backupset table. However, modifying system tables shoshould always be done with caution.