마지막 백업 시간 및 DBCC 확인

마지막 백업 시간 및 DBCC 확인

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 최근 마지막 백업은 언제일까? 마지막으로 성공한 DBCC는 언제일까? 아마 대부분의 DBA들은 백업 파일을 이름(날짜로 이름이 생성되어 있는 경우) 또는 파일의 생성날짜로 백업 시간을 알아 낼 것이다.

 

다음 스크립트를 사용하여 현재 생성되어 있는 데이터베이스의 마지막 백업 날짜 및 DBCC 날짜를 확인하여보자.

SET NOCOUNT ON GO   USE master GO   -- Trace flag to make DBCC Page command results available in the current connection   DBCC TRACEON(3604) GO   CREATE TABLE #DBCC_table ( ParentObject nvarchar(4000) null, Object nvarchar(4000) null, Field nvarchar(4000) null, VALUE nvarchar(4000) null )   CREATE TABLE #LastDBCC_table ( [Database Name] nvarchar(4000) null, [Last Known Good DBCC] nvarchar(4000) null )   DECLARE @cmd varchar(4000) DECLARE @DB_NAME nvarchar(500) DECLARE @DB_ID int DECLARE LastDBCC_cursor CURSOR FOR   SELECT name, [dbid] FROM sysdatabases ORDER BY dbid     OPEN LastDBCC_cursor -- Perform the first fetch. FETCH NEXT FROM LastDBCC_cursor into @DB_NAME, @DB_ID -- Check @@FETCH_STATUS to see if there are any more rows to fetch.       WHILE @@FETCH_STATUS = 0     BEGIN         -- This is executed as long as the previous fetch succeeds.         SET @cmd = 'dbcc page('+ convert(varchar,@DB_ID)+',1,9,3) with tableresults'         insert into #DBCC_table execute (@cmd)         insert into #LastDBCC_table         select distinct @DB_NAME, VALUE from #DBCC_table where Field = 'dbi_dbccLastKnownGood'                if @@ROWCOUNT = 0                     insert into [#LastDBCC_table] select @DB_NAME, 'Not implemented'           FETCH NEXT FROM LastDBCC_cursor into @DB_NAME, @DB_ID           delete #DBCC_table     END   CLOSE LastDBCC_cursor   DEALLOCATE LastDBCC_cursor     select     T1.[Database Name],     CASE     WHEN (max(T1.[Last Known Good DBCC]) = '1900-01-01 00:00:00.000') then 'Not Yet Ran'     ELSE max(T1.[Last Known Good DBCC]) END as [Last Known Good DBCC],     --max(T1.[Last Known Good DBCC]) as [Last Known Good DBCC],     COALESCE(convert(varchar(50),MAX(T2.backup_finish_date),21),'Not Yet Taken') AS [Last BackUp Taken] from #LastDBCC_table T1 LEFT OUTER JOIN msdb.dbo.backupset T2     ON T2.database_name = T1.[Database Name] GROUP BY T1.[Database Name] ORDER BY T1.[Database Name]   DROP TABLE #LastDBCC_table DROP TABLE #DBCC_table   DBCC traceoff(3604) GO

 

 

 

[참고자료]

http://blogs.msdn.com/b/sql_pfe_blog/archive/2010/02/09/last-known-good-backup-dbcc.aspx

 

 

2013-10-29 / 강성욱 / http://sqlmvp.kr