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

2010/01/27 14:00

Zasfe 윈도우 backup,mssql 2000,MSSQL2K,백업,히스토리 삭제 View Comments


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)

0 Comments (+add yours?)

Tracbacks (+view to the desc.)

Trackback Address ::
트랙백 주소 :: http://zasfe.com/173/trackback/

Newer Entries Older Entries