Disaster recovery - Identify missing backups

Compatible from SQL Server 2008

Quick check if all your databases are backed up. It is important for every DBA that databases you are responsible of are properly backed up. Use this script to identify if there is not any database with missing backups. You backup strategy is very important so take care about filling its requirements. You have to be prepared for every recovery scenarios.

TIP: Script is checking all databases without backup during last 24 hours. This can vary based on you backup strategy so change value in HAVING condition on line 11 to best fit your case.

--Databases with data backup over 24 hours old 
SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server], 
	b.database_name AS [Database], 
   MAX(b.backup_finish_date) AS [Last Data Backup Date],  
   DATEDIFF(hh, MAX(b.backup_finish_date), GETDATE()) AS [Backup Age (Hours)] 
FROM msdb.dbo.backupset b
WHERE b.type = 'D'  
GROUP BY b.database_name 
HAVING (MAX(b.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))  
UNION  
--Databases without any backup history 
SELECT      
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],  
   d.name AS [Database],  
   NULL AS [Last Data Backup Date],  
   9999 AS [Backup Age (Hours)]  
FROM master.dbo.sysdatabases d
	LEFT JOIN msdb.dbo.backupset b ON d.name  = b.database_name 
WHERE b.database_name IS NULL 
	AND d.name <> 'tempdb' 
ORDER BY b.database_name 

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
Servernvarchar(128)Both the Windows server and instance information associated with a specified instance of SQL Server. NULL = Input is not valid, or an error.
Databasenvarchar(128)Name of the database involved in the backup operation. Can be NULL.
Last Data Backup DatedatetimeLast time when backup operation finished.
Backup Age (Hours)intNumber of hours since last backup was taken.

Leave a Reply

Your email address will not be published. Required fields are marked *