Remove Backup History 

Posted by Philip Leitch Monday, November 09, 2009 6:11:50 PM
Previous Post << >> Next Post
 
 
NOTE - there  are two procedures here, the first for SQL Server 2000, the second for 2005/2008

SQL Server 2000

CREATE PROC isp_DeleteBackupHistory
(@DaysToRetain int)
AS

SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int

BEGIN TRAN

 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)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 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)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 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)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 SELECT media_set_id, backup_finish_date
 INTO #Temp
 FROM msdb..backupset
 WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 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
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupset
 FROM msdb..backupset bs
 INNER JOIN #Temp t
 ON bs.media_set_id = t.media_set_id

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 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

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupmediaset
 FROM msdb..backupmediaset bms
 INNER JOIN #Temp t
 ON bms.media_set_id = t.media_set_id

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc

SQL Server 2005 or 2008

CREATE PROCEDURE [dbo].[Delete_Backup_History]
(@days_to_retain int = 186)
AS
BEGIN
SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int
DECLARE @target_date datetime

SET @target_date = GETDATE() - @days_to_retain

BEGIN TRAN

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 < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

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 < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

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 < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfilegroup
FROM msdb..backupfilegroup bfg
INNER JOIN msdb..backupset bs ON bfg.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

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
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupset
FROM msdb..backupset bs
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

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
WHERE NOT EXISTS (SELECT media_set_id FROM msdb..backupset bs
WHERE bs.media_set_id = t.media_set_id
AND bs.backup_finish_date >= @target_date)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
WHERE NOT EXISTS (SELECT media_set_id FROM msdb..backupset bs
WHERE bs.media_set_id = t.media_set_id
AND bs.backup_finish_date >= @target_date)


SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc
END




Copyright 2009 Philip Leitch
Previous Post << >> Next Post