'backup'에 해당되는 글 2건

  1. 2010.01.28 백업기록 조회
  2. 2010.01.27 2년전 백업 정보 삭제 쿼리(mssql 2000)
분류없음2010.01.28 08:00
MSSQL 2005 이상부터 사용이 가능합니다.

IF OBJECT_ID('TEMPDB..#TMP_BACKUPHISTORY') IS NOT NULL
    DROP TABLE #TMP_BACKUPHISTORY

SELECT *
  INTO #TMP_BACKUPHISTORY
  FROM (
select s.server_name, s.database_name, workname = s.name, s.backup_set_id, s.backup_finish_date,
mdf_file=f.physical_name , backup_file = m.physical_device_name ,f.file_size
from msdb..backupset s, msdb..backupmediafamily m , msdb..backupfile f
where s.media_set_id=m.media_set_id and f.backup_set_id=s.backup_set_id
and s.type='D' and f.file_type='D' and has_dbaccess(s.database_name)=1
and s.database_name not in (N'tempdb', N'model', N'msdb')
              and s.backup_finish_date > DATEADD(dd,-7,CONVERT (char(10), CURRENT_TIMESTAMP, 20))
) A
select * from #TMP_BACKUPHISTORY

DROP TABLE #TMP_BACKUPHISTORY
Posted by Zasfe
컴퓨터2010.01.27 14:00

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)
Posted by Zasfe

티스토리 툴바