[转]Backup and restore history details

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 (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 a
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 could run something like this to find out the last 10 transaction log backups for a particular database:

SELECT TOP 20 b.physical_device_name, a.backup_start_date, a.first_lsn, a.user_name FROM msdb..backupset a
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 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:

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 a
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 3:30'
ORDER BY a.backup_start_date, b.family_sequence_number

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.

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 in the msdb database, not the master database.

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 from a on 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 should always be done with caution.