Delete database backup history in SQL Server by serverku

As I have written the article to clean up the old database backup for the maintenance activities. The same thing i am doing here as a maintenance activity, but not for the clean up of database backups, It is for database backups history cleanup from MSDB database. Please read my earlier posts related database backup, you may like them.

1. Database Backup Statistics and History
2. Archive old database backup files using forfiles.exe and FOR /F
3. Archive old database backup files using TSQL Script

It is required to clean up the database backup history periodically if no longer needed. Because if the msdb database has more history then it will take the time to load it or sometime it will fail to load because of time out. The script to clean up the database backups, history and a demo are as follows, First we will look for the history of all the database backups done by this SQL instance.

USE msdb 
GO

SELECT bs.database_name, 
       bs.backup_start_date, 
       CASE bs.type 
         WHEN 'D' THEN 'Full Backup' 
         WHEN 'I' THEN 'Diff Backup' 
         WHEN 'L' THEN 'Log Backup' 
       END AS BackupType, 
       bmf.physical_device_name 
FROM   msdb..backupset bs 
       INNER JOIN msdb..backupmediafamily bmf 
               ON ( bs.media_set_id = bmf.media_set_id ) 
ORDER  BY bs.backup_start_date DESC 

We have two methods to delete the database backup history.

#1. Based on DateTime By passing the date in stored procedure , SP will delete all the database history older then specified date time. Let us run below query and get the output.

DECLARE @BackupOlderdate DATETIME
SET @BackupOlderdate = '2011-05-17 14:22:13.000'
EXEC sp_delete_backuphistory @BackupOlderdate
GO

#2. Based on Database
By passing the Database name in stored procedure, SP will delete all the database history for specified database only. Let us run below query and get the output.

DECLARE @DBname sysname
SET @DBname = 'trialmaster'
EXEC sp_delete_database_backuphistory @DBname
GO

Please comment here if you know any other methods to delete it.