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 a.name, b.type, MAX(b.backup_finish_date)
LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2))
IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name =
b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.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.
|
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' |
Document history
20100920 Modified first query to also show databases with no
9/20/2010 | Modified first query to also show databases with no backups. |
10/8/2008 | Added sp_delete_database_backuphistory example. |
7/13/2008 | Added example to retrieve transaction log sequence. |
20080711 Added example to purge SQL Server 2000 history
7/11/2008 | Added example to purge SQL Server 2000 history tables. |
6/27/2008 | Initial release. |