Daily Backup Check

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 8 Jun 2020

Description

List the latest database and log backup dates and highlight those that are potentially overdue. See SP comments for the overdue criteria

Code

USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DailyBackupCheck]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_DailyBackupCheck]GOCREATE PROC [dbo].[usp_DailyBackupCheck]AS/*************************************************************************** Purpose: To create a list of backups that are overdue. Full, Differential and Log backups are considered. An overdue status would be: 1a. Full Backup not taken for 24 hours where there is no Differential backup 1b. Full backup not taken for 7 days where there is has been Differential Backup within the last 24 hours 2. Differential backup not taken for the last 24 hours 3. Log backup not taken for the past 6 hours This script will only work automatically for backup schemes that have performed at least one successful backup of each type required This is an acceptable limitation, as when setting up/changing backups, one should manually check the first few cycles anyway Author: ChillyDBA History: 10 May 2012 - Initial Issue ****************************************************************************/CREATE TABLE #BackupList1(DatabaseName SYSNAME,BackupType CHAR,LastBackupTaken DATETIME,UserName VARCHAR(100))CREATE TABLE #BackupList2(DatabaseName SYSNAME,LastFullBackupTaken DATETIME,LastDiffBackupTaken DATETIME,LastLogBackupTaken DATETIME,FullOverdue BIT DEFAULT 0,DiffOverdue BIT DEFAULT 0,LogOverdue BIT DEFAULT 0)CREATE TABLE ##MailBody(LineID INT IDENTITY(1,1),LineText VARCHAR(1000))DECLARE @Now DATETIME, @Cmd VARCHAR(1000)SELECT @Now = GETDATE(), @Cmd = ''-- get raw backup details, but only consider the past 2 months. -- if it has been worng for longer, then tough! -- THis screens out DBs that might have changed backup type some time ago INSERT #BackupList1(DatabaseName,BackupType,LastBackupTaken,UserName)SELECTT1.Name as DatabaseName,t2.type,MAX(T2.backup_finish_date) as LastBackUpTaken,MAX(T2.user_name)as UserNameFROM sys.databases T1LEFT OUTER JOIN msdb.dbo.backupset T2ON T2.database_name = T1.nameWHERE T1.state_desc = 'ONLINE'AND backup_finish_date > DATEADD(mm, -2, GETDATE())GROUP BY T1.Name, t2.typeORDER BY T1.Name, t2.typeINSERT #BackupList2(DatabaseName,LastFullBackupTaken,LastDiffBackupTaken,LastLogBackupTaken)SELECTx.DatabaseName,MAX(x.LastFullBackupTaken),MAX(x.LastDiffBackupTaken),MAX(x.LastLogBackupTaken)FROM(SELECTDatabaseName AS DatabaseName,LastBackupTaken AS LastFullBackupTaken,NULL AS LastDiffBackupTaken,NULL AS LastLogBackupTakenFROM #BackupList1WHERE BackupType = 'D'UNIONSELECTDatabaseName AS DatabaseName,NULL AS LastFullBackupTaken,LastBackupTaken AS LastDiffBackupTaken,NULL AS LastLogBackupTakenFROM #BackupList1WHERE BackupType = 'I'UNIONSELECTDatabaseName AS DatabaseName,NULL AS LastFullBackupTaken,NULL AS LastDiffBackupTaken,LastBackupTaken AS LastLogBackupTakenFROM #BackupList1WHERE BackupType = 'L') as xGROUP BY x.DatabaseName-- Identify states 1a and 1b UPDATE #BackupList2SET FullOverdue = 1WHERE(((DATEDIFF(hh, LastFullBackupTaken, @Now) > 24) AND (LastDiffBackupTaken IS NULL))OR((DATEDIFF(hh, LastFullBackupTaken, @Now) > 168) AND (DATEDIFF(hh, LastDiffBackupTaken, @Now) <= 24)))-- Identify state 2 UPDATE #BackupList2SET DiffOverdue = 1WHERE((DATEDIFF(hh, LastDiffBackupTaken, @Now) > 24))-- Identify state 3 UPDATE #BackupList2SET LogOverdue = 1WHERE((DATEDIFF(hh, LastLogBackupTaken, @Now) > 6))/* --Some test broken backups update #BackupList2 set fulloverdue = 1 where databasename = 'aac' update #BackupList2 set diffoverdue = 1 where databasename = 'Cognos_10_P_Motio' update #BackupList2 set logoverdue = 1 where databasename = 'BESMgmt5QA' update #BackupList2 set fulloverdue = 1 , logoverdue = 1 where databasename = 'Cognos_83' update #BackupList2 set fulloverdue = 1 , diffoverdue = 1, logoverdue = 1 where databasename = 'fest' */INSERT ##MailBody(LineText)VALUES ('The following database backups are overdue:') ;INSERT ##MailBody(LineText)VALUES ('') ;INSERT ##MailBody(LineText)SELECTCHAR(9)+ CHAR(9)+ DatabaseName+ REPLICATE(CHAR(9), (50 - DATALENGTH(DatabaseName) + 4)/8)+ ' ==> ::'+ CASE WHEN FullOverdue = 1 THEN ' FULL ::' ELSE '' END+ CASE WHEN DiffOverdue = 1 THEN ' DIFF ::' ELSE '' END+ CASE WHEN LogOverdue = 1 THEN ' LOG ::' ELSE '' END+ ' <=='FROM #BackupList2WHERE CAST(FullOverdue AS INT) + CAST(DiffOverdue AS INT) + CAST(LogOverdue AS INT) > 0--SET @cmd = 'SELECT LineText FROM ##MailBody ORDER By LineID' ; --IF (SELECT COUNT(*) FROM ##MailBody) > 2 --BEGIN ----select 'send mail' -- EXEC msdb.dbo.sp_send_dbmail -- @profile_name = N'myprofile', -- @recipients = N'myemail@mycompany.ca', -- @subject = 'Server: [' + @@SERVERNAME + N'] - Daily Backup Status Check ' , -- @query = @cmd, -- @query_result_header = 0 SELECT * FROM #BackupList1SELECT * FROM #BackupList2SELECT * FROM ##MailBodyENDDROP TABLE #BackupList1DROP TABLE #BackupList2DROP TABLE ##MailBody