Archive for the ‘History Cleanup’ Category

How to delete the database backup history?

Every time a database backup is ran, entries are logged in the MSDB.DBO.BACKUPFILE, MSDB.DBO.BACKUPFILEGROUP, MSDB.DBO.BACKUPMEDIAFAMILY, MSDB.DBO.BACKUPMEDIASET, and MSDB.DBO.BACKUPSET system tables of the MSDB system database. To see the backup histories for all the databases in the instance, you can run the following query:

-- Querying the database backup histories in the instance
SELECT @@SERVERNAME as InstanceName
,bs.database_name as DatabaseName
,bmf.physical_device_name as BackupPath
,bs.type as BackupType
,bs.backup_start_date as BackupStartDate
,bs.backup_finish_date as BackupFinishDate
,CAST(bs.backup_size / 1024 / 1024 as DECIMAL(18,2)) as BackupSizeMB
FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf
	ON bs.media_set_id = bmf.media_set_id
ORDER BY bs.backup_finish_date desc

You can use the MSDB.DBO.SP_DELETE_BACKUPHISTORY system stored proc to delete backup histories to reclaim disk space if necessary. Prior to deleting any backup histories, make sure you know your business rules and requirements for the backup histories and the retention day. The following script is an example of how the backup histories can be deleted:

-- Remove the database backup history based on the retention days you wish to keep
-- Must must know your business rules before setting your retention days
DECLARE @RetentionDays INT
,@CutOffDate VARCHAR(10)

SET @RetentionDays = 31 -- change the retention days here
SET @CutOffDate = CONVERT(VARCHAR(10), GETDATE()-@RetentionDays, 112)

-- delete the job history based on the cut off date
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @CutOffDate

REFERENCES:

sp_delete_backuphistory
http://msdn.microsoft.com/en-us/library/ms188328(v=sql.105).aspx

backupset
http://msdn.microsoft.com/en-us/library/ms186299(v=sql.105).aspx

backupmediafamily
http://msdn.microsoft.com/en-us/library/ms190284(v=sql.105).aspx