Script to find out when last back is taken and when a last successful DBCC CHECKDB is executed…


Use the below code to identify the last backup execution time and last time when
DBCC CHECKDB was successfully executed (which means when last DBCC CHECKDB executed last time and reported no corruption) on all databases of the SQL Instance. This code will work (for CHECKDB part) only from SQL Server 2005 onwards.

Typically I use this handy code for server’s health check or for some ad-hoc activity where these informations are needed immediately. You may use this code in many cases as per requirement of your environment. If you are interested on DBCC part of this code, please refer to http://blog.consultdba.com/2010/04/identifying-when-last-time-dbcc-checkdb.html#links for details.

SET NOCOUNT ON
GO
USE master
GO
DBCC TRACEON(3604)
GO
CREATE TABLE #tbl_DBCC (
ParentObject nvarchar(4000) null,
Object nvarchar(4000) null,
Field nvarchar(4000) null,
VALUE nvarchar(4000) null
)
CREATE TABLE #tbl_LastDBCC (
[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
      FETCH NEXT FROM LastDBCC_cursor into @DB_NAME, @DB_ID
      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @cmd = 'dbcc page('+ convert(varchar,@DB_ID)+',1,9,3) with tableresults'
      insert into #tbl_DBCC execute (@cmd)
      insert into #tbl_LastDBCC
      select distinct @DB_NAME, VALUE
      from #tbl_DBCC
      where Field = 'dbi_dbccLastKnownGood'
      if @@ROWCOUNT = 0
           insert into [#tbl_LastDBCC] select @DB_NAME, 'Not implemented'

      FETCH NEXT FROM LastDBCC_cursor into @DB_NAME, @DB_ID
      delete #tbl_DBCC
      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 'DBCC Not executed yet!'
                  ELSE max(T1.[Last Known Good DBCC])
            END as [Last Known Good DBCC],
            COALESCE(convert(varchar(50),MAX(T2.backup_finish_date),21),'No Backup Taken Yet!') AS [Last BackUp Taken]
      from #tbl_LastDBCC 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 #tbl_LastDBCC
      DROP TABLE #tbl_DBCC
      DBCC traceoff(3604)
GO

Reference taken: PFE SQL Server Blog to develop the above code.

Comments