Home

Backup and restore history details

Prev Page Next Page
Introduction
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Failover
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Failover
Log shipping in SQL Backup
Using the CopyTool utility
Failover
3rd party backup applications
VDI
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Encryption
Integrated database verification
Database file relocation
Improved backup retention
RESTORE HELP
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
Pending
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship




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

20080711 Added example to purge SQL Server 2000 history

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.    
7/11/2008    Added example to purge SQL Server 2000 history tables.    
6/27/2008    Initial release.    
 
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.