/************************************************************/ /*************** Missing Backup Report **********************/ /***** We can change parameters to customize the report *****/ /************************************************************/ DECLARE @Backup_Type VARCHAR(20), @Days SMALLINT; -- D - Full; I - Differential; L- Transaction Log SET @Backup_Type = 'D'; -- Number of days -- Ex: -1: Reports all DB without backup in last 24 hours -- Ex: -30 - Reports all DB without any backup from last 30 Days SET @Days = -1; --Database with no Backup in the given period SELECT SERVERPROPERTY('Servername') AS 'Server_Name', bs.database_name AS 'Database_Name', MAX(bs.backup_finish_date) AS 'Last_Backup_Date', DATEDIFF(hh, MAX(bs.backup_finish_date), GETDATE()) AS 'Backup_Age(Hrs)' FROM msdb.dbo.backupset bs WHERE bs.type = @Backup_Type GROUP BY bs.database_name HAVING (MAX(bs.backup_finish_date) < DATEADD(DD, @Days, GETDATE())) UNION -- Databases with no backup history SELECT SERVERPROPERTY('Servername') AS 'Server_Name', sd.name AS 'Database_Name', NULL AS 'Last_Backup_Date', 9999 AS 'Backup_Age(Hrs)' FROM master.dbo.sysdatabases sd LEFT JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name WHERE bs.database_name IS NULL AND sd.name <> 'tempdb' ORDER BY bs.database_name;