DBA Backup Litmus Test

--DBA Backup LITMUS TEST
--last time DBCC CHECKDB finished successfully on each database  ******
CREATE TABLE #temp
    (
      ParentObject VARCHAR(255) ,
      [Object] VARCHAR(255) ,
      Field VARCHAR(255) ,
      [Value] VARCHAR(255)
    )   
 
CREATE TABLE #DBCCResults
    (
      ServerName VARCHAR(255) ,
      DBName VARCHAR(255) ,
      LastCleanDBCCDate DATETIME
    )   
 
EXEC master.dbo.sp_MSforeachdb @command1 = 'USE [?]; INSERT INTO #temp EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')',
    @command2 = 'INSERT INTO #DBCCResults SELECT @@SERVERNAME, ''?'', Value FROM #temp WHERE Field = ''dbi_dbccLastKnownGood''',
    @command3 = 'TRUNCATE TABLE #temp';
 
   --Delete duplicates due to a bug in SQL Server 2008
 
WITH    DBCC_CTE
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY ServerName, DBName,
                                            LastCleanDBCCDate ORDER BY LastCleanDBCCDate ) RowID
               FROM     #DBCCResults
             )
    DELETE  FROM DBCC_CTE
    WHERE   RowID > 1 ;
--Recovery Models and DBA Backup Litmus Test)
SELECT 
	@@SERVERNAME AS [server]
	,dd.name AS [database_name]
	, dd.recovery_model_desc
	,(SELECT SUM((size*8)/1024) SizeMB
		FROM sys.master_files
		WHERE DB_NAME(database_id)=dd.name AND type_desc = 'ROWS') AS database_size_mb
	,'Production' AS server_purpose
	
	,(SELECT TOP 1  MAX(b.backup_finish_date) AS Last_full_backup_finish_date
		FROM    master.sys.databases d WITH (NOLOCK)
		LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK) ON d.name = b.database_name AND b.type = 'D'
		WHERE d.name <> 'tempdb' AND is_copy_only = 0
			AND d.name=dd.name
		GROUP BY d.name
		ORDER BY Last_full_backup_finish_date) AS last_full_backup_finish_date
	
	,(SELECT TOP 1  MAX(b.backup_finish_date) AS Last_differential_backup_finish_date
		FROM    master.sys.databases d WITH (NOLOCK)
		LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK) ON d.name = b.database_name AND b.type = 'I'
		WHERE d.name <> 'tempdb' AND is_copy_only = 0
			AND d.name=dd.name
		GROUP BY d.name
		ORDER BY Last_differential_backup_finish_date) AS last_differential_backup_finish_date
	
	,(SELECT TOP 1  MAX(b.backup_finish_date) AS Last_log_backup_finish_date
		FROM    master.sys.databases d WITH (NOLOCK)
		LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK) ON d.name = b.database_name AND b.type = 'L'
		WHERE d.name <> 'tempdb' AND is_copy_only = 0
			AND d.name=dd.name and d.recovery_model = 1
		GROUP BY d.name
		ORDER BY Last_log_backup_finish_date) AS last_log_backup_finish_date
	
	,DATEDIFF(MINUTE,(SELECT TOP 1  MAX(b.backup_finish_date) AS Most_recent_backup
		FROM    master.sys.databases d WITH (NOLOCK)
		LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK) ON d.name = b.database_name 
		WHERE d.name <> 'tempdb' AND is_copy_only = 0
			AND d.name=dd.name 
		GROUP BY d.name
		ORDER BY Most_recent_backup),GETDATE()) AS recent_backup_occurred_how_many_minutes_ago
		,(SELECT TOP 1 LastCleanDBCCDate 
			FROM    #DBCCResults
			WHERE DBName=dd.name
			ORDER BY LastCleanDBCCDate) AS last_clean_DBCC_CHECKDB_date
FROM    master.sys.databases dd WITH (NOLOCK)
WHERE dd.name NOT IN ( 'tempdb')

DROP TABLE #temp, #DBCCResults ;

 

Resources:

http://www.brentozar.com/archive/2015/06/how-do-you-manage-dbas-measuring-backups/

http://www.brentozar.com/archive/2012/03/how-talk-your-boss-about-world-backup-day/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: