Use the below code to identify the last backup execution time and last time when
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
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!'
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
GO
Reference taken: PFE SQL Server Blog to develop the above code.
Comments