SQLBackupRestore - 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 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 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 forum post 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.

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.

EXEC msdb..sp_delete_database_backuphistory 'AdventureWorks'

Discuss or comment on this article on our Facebook group.

Tell your friends on Facebook about this article.

Document history

10/8/2008	Added sp_delete_database_backuphistory example.
7/13/2008	Added example to retrieve transaction log sequence.
7/11/2008	Added example to purge SQL Server 2000 history tables.
6/27/2008	Initial release.