Deleting backup history manually

Category: How do I?
Date: 01 Feb 2011
Product: SQL Backup
How can the backup and restore history stored in SQL Backup be removed after a certain period of time?

There are two methods of deleting the backup history:
1. Using SQL Backup, enable the 'Delete old backup history' option available in the SQL Backup GUI->Tools Menu->Option->File Management Tab. SQL Backup will delete the old history depending upon the "Older than" value set.

On servers that run SQL Backup very frequently, there is a known issue where a deadlock occurs because SQL Backup is attempting to retrieve LSN data at the same time a backup history cleanup is taking place. In that circumstance, it's desirable to turn off automatic backup history deletion (as in step 1) and create an independent job to delete the backup history on a less frequent schedule, say once a day.

2. In version 4 of SQL Backup, use the Stored Procedure msdb..sp_delete_backuphistory from Query Analyzer or New Query within SSMS. See the following SQL Server Books On-Line article:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bdb56834-616e-47e4-b942-e895d2325e97.htm

In version 5 of SQL Backup, the history is also cached on the server, so the history deletion must include data in the cache. The following SQL code can be used in a SQL Server scheduled job to delete both the backup history from the MSDB database as well as the local cache file from version 5 of SQL Backup for backup and restore operations that have occurred more than the specified number of days ago.

----------------------------------------------------------------------------------------------------
-- Red-Gate delete old backup history from local (mobile/compact) database
----------------------------------------------------------------------------------------------------
SET NOCOUNT ON

DECLARE @backup_id INT
, @command NVARCHAR(4000)
, @retain_date NVARCHAR(64)
, @IDList VARCHAR(2000)

-- number of days of history that you want to retain
SET @retain_date = GETDATE() - 60

-- temp table for ids we're deleting.
IF OBJECT_ID('tempdb..#DeleteTheseIDs') IS NOT NULL
DROP TABLE #DeleteTheseIDs
CREATE TABLE #DeleteTheseIDs ( id INT )

-- get total from the local(mobile/compact) (this will also show an error if its corrupted)
EXEC [master]..sqbdata N'select COUNT(*) AS TotalBackupRows from backuphistory'
EXEC [master]..sqbdata N'select COUNT(*) AS TotalRestoreRows from restorehistory '

----------------------------------------------------------------------------------------------------
-- delete backup history - standard
----------------------------------------------------------------------------------------------------
-- first do a standard delete
EXEC msdb.dbo.sp_delete_backuphistory @retain_date

----------------------------------------------------------------------------------------------------
-- delete backup history - Red-Gate Cache
----------------------------------------------------------------------------------------------------

-- get all of the ids that we want to delete
SET @command = 'select id from backuphistory where backup_end < ''' + @retain_date + ''''

TRUNCATE TABLE #DeleteTheseIDs
INSERT INTO #DeleteTheseIDs
EXEC [master]..sqbdata @command

SELECT 'Deleteing # Backup ids ' = COUNT(*) FROM #DeleteTheseIDs

-- loop until they're all deleted
WHILE EXISTS (SELECT 1 FROM #DeleteTheseIDs)
BEGIN

-- get next set of ids
-- (do not get too many at a time, otherwise the list will be truncated)
SET @IDList = ''
SELECT TOP 150 @IDList = @IDList
       + CASE WHEN @IDList = '' THEN '' ELSE ',' END
       + CONVERT(VARCHAR(10), id)
   FROM #DeleteTheseIDs
   ORDER BY id

-- delete history
    SET @command = 'delete from backupfiles where backup_id IN (' + @IDList + ')'
    EXEC [master]..sqbdata @command
    SET @command = 'delete from backuplog where backup_id IN (' + @IDList + ')'
    EXEC [master]..sqbdata @command
    SET @command = 'delete from backuphistory where id IN (' + @IDList + ')'
    EXEC [master]..sqbdata @command

-- delete from temp file
SET @Command = 'DELETE FROM #DeleteTheseIDs where id IN (' + @IDList + ')'
EXEC (@Command)

END

----------------------------------------------------------------------------------------------------
-- delete restore history - Red-Gate Cache
----------------------------------------------------------------------------------------------------

-- get all of the ids that we want to delete
SET @command = 'select id from restorehistory where restore_end < ''' + @retain_date + ''''

TRUNCATE TABLE #DeleteTheseIDs
INSERT INTO #DeleteTheseIDs
EXEC [master]..sqbdata @command

SELECT 'Deleting # Restore ids ' = COUNT(*) FROM #DeleteTheseIDs

-- loop until they're all deleted
WHILE EXISTS (SELECT 1 FROM #DeleteTheseIDs)
BEGIN

-- get next set of ids
SET @IDList = ''
SELECT TOP 200 @IDList = @IDList
       + CASE WHEN @IDList = '' THEN '' ELSE ',' END
       + CONVERT(VARCHAR(10), id)
   FROM #DeleteTheseIDs
   ORDER BY id

-- delete history
    SET @command = 'delete from restorefiles where restore_id IN (' + @IDList + ')'
    EXEC [master]..sqbdata @command
    SET @command = 'delete from restorelog where restore_id IN (' + @IDList + ')'
    EXEC [master]..sqbdata @command
    SET @command = 'delete from restorehistory where id IN (' + @IDList + ')'
    EXEC [master]..sqbdata @command

-- delete from temp file
SET @Command = 'DELETE FROM #DeleteTheseIDs where id IN (' + @IDList + ')'
EXEC (@Command)

END

SELECT 'Done.'