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.