2년전 백업 정보 삭제 쿼리(mssql 2000)

DECLARE @backupID int

DECLARE @mediaID int

DECLARE @restoreID int

DECLARE @year char(4)

DECLARE @month char(2)

DECLARE @day char(2)

DECLARE @deleteday char(10)

 

SET @year = year(getdate())-2 -- 2년전

SET @month = month(getdate())

SET @day = day(getdate())

IF len(@month) = 1

SET @month = '0'+ @month

IF len(@day) = 1

SET @day = '0'+ @day

SET @deleteday = @year +'-'+ @month +'-'+ @day

SELECT TOP 1 @backupID = backup_set_id, @mediaID = media_set_id from msdb..backupset

where

backup_finish_date > @deleteday

SELECT TOP 1 @restoreID = restore_history_id From msdb..restorehistory

Where

restore_date > @deleteday

delete from msdb..restorefile where restore_history_id < @restoreID

delete from msdb..restorefilegroup where restore_history_id < @restoreID

delete from msdb..restorehistory where restore_history_id < @restoreID or backup_set_id < @backupID

delete from msdb..backupfile where backup_set_id < @backupID

delete from msdb..backupmediafamily where media_set_id < @mediaID

delete from msdb..backupset where backup_set_id < @backupID

use msdb

DBCC SHOWCONTIG

DBCC DBREINDEX (restorefile, '', 100)

DBCC DBREINDEX (restorefilegroup, '', 100)

DBCC DBREINDEX (restorehistory, '', 100)

DBCC DBREINDEX (backupfile, '', 100)

DBCC DBREINDEX (backupmediafamily, '', 100)

DBCC DBREINDEX (backupset, '', 100)