CREATE OR ALTER PROC DbBackupReport
AS
BEGIN
SELECT @@SERVERNAME AS serverName, D.[name] AS databaseName, D.recovery_model_desc,
MAX(CASE WHEN B.[type] = 'D' THEN B.backup_finish_date ELSE NULL END) AS LastFullBackup,
MAX(CASE WHEN B.[type] = 'I' THEN B.backup_finish_date ELSE NULL END) AS LastDiffBackup,
MAX(CASE WHEN B.[type] = 'L' THEN B.backup_finish_date ELSE NULL END) AS LastLogBackup
INTO #Result
FROM master.sys.databases D LEFT JOIN msdb.dbo.backupset B ON D.[name] = B.[database_name]
WHERE D.[name] NOT IN ('tempdb', 'model')
GROUP BY D.[name], D.recovery_model_desc;
SELECT serverName, databaseName, recovery_model_desc,
ISNULL(LastFullBackup, '19000101') AS LastFullBackup, LastDiffBackup, LastLogBackup,
CASE WHEN recovery_model_desc <> 'SIMPLE' AND ISNULL(LastFullBackup, '19000101') < DATEADD(day, -7, GETDATE()) THEN 'NO' ELSE 'YES' END AS NonSimpleModeFullBackupin7Days,
CASE WHEN ISNULL(LastFullBackup, '19000101') < DATEADD(hour, -24, GETDATE()) AND
ISNULL(LastDiffBackup, '19000101') < DATEADD(hour, -24, GETDATE())
THEN 'NO' ELSE 'YES' END AS FullOrDiffBackupIn24Hours,
CASE WHEN recovery_model_desc <> 'SIMPLE' AND ISNULL(LastLogBackup, '19000101') < DATEADD(hour, -24, GETDATE()) THEN 'NO' ELSE 'YES' END AS NonSimpleModeLogIn24Hours
INTO #Output FROM #Result;
SELECT *, CASE WHEN 'NO' IN (NonSimpleModeFullBackupin7Days, FullOrDiffBackupIn24Hours, NonSimpleModeLogIn24Hours) THEN 'Red' ELSE 'Black' END AS HighLight FROM #Output ORDER BY HighLight DESC;
END
GO
EXEC DbBackupReport;
You can use the "HighLight" column values in the stored procedure result, to make a custom formatting in the SSRS report, to highlight the databases that need your concern.
