카테고리 없음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

댓글을 달아 주세요