Credits:
Author: Unknown (extended version by ChillyDBA)
Date: 17 Feb 2012
Description
This code retrieves a list of the last time each database on a server had a full backup.
If the backup was taken over 7 days ago, then the status column will show 'WARNING'
If the backup file is not available (either not present or no access granted under the current security context), then Not Available will be returned.
Also, if the backup was performed by an external product, the file will also likely show as not available eg with Veritas NetBackup, the file name will take the form VNBxxxxxx and will show as not available.
I also created an enhanced version of the code for use in an environment where backups are out of my direct control, and currently 'in between owners', in a domain setup where there is little in the way of coherent centralised monitoring. It allowed me to get a heads up if the Enterprise Backup software blew a gasket.
- Extended to encompass TXLog Backups
- Includes differential backups
- Can email out the results
Details of the operating parameters can be found in the code comments
Note that both pieces of code will only work using backup history metadata that has not been subject to cleardown/archiving.
Code
Full backups only:
SET NOCOUNT ON GO DECLARE @Counter SMALLINT, @DBName SYSNAME, @DB_BKPdate VARCHAR(100), @Status VARCHAR(20), @ServerName SYSNAME, @Media_Set_ID VARCHAR(20), @FilePath VARCHAR(1000), @FileStatus INT, @FileAvailable VARCHAR(20), @BackupSize FLOAT
CREATE TABLE #Backup_Details ( ServerName SYSNAME, DatabaseName SYSNAME, BkpDate VARCHAR(20) NULL, BackupSize_in_MB VARCHAR(20), Status VARCHAR(20), FilePath VARCHAR(1000), FileAvailable VARCHAR(20) )
SELECT @ServerName = CAST(SERVERPROPERTY('ServerName')AS SYSNAME)
-- start looping through all databases SELECT @Counter=MAX(dbid) FROM MASTER..sysdatabases
WHILE @Counter > 0 BEGIN /* Need to re-initialize all variables*/ SELECT @DBName = NULL , @DB_BKPdate = NULL , @Media_Set_ID = NULL , @BackupSize = NULL , @FilePath = NULL , @FileStatus = NULL , @FileAvailable = NULL , @Status = NULL , @BackupSize = NULL SELECT @DBName = name FROM MASTER..sysdatabases WHERE dbid = @Counter SELECT @DB_BKPdate = MAX(backup_start_date) FROM msdb..backupset WHERE database_name = @DBName AND TYPE='D' SELECT @Media_Set_ID = media_set_id, @BackupSize = backup_size FROM msdb..backupset WHERE backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb..backupset WHERE database_name = @DBName AND TYPE='D' ) AND database_name = @DBName AND TYPE='D' SELECT @FilePath = physical_device_name FROM msdb..backupmediafamily WHERE media_set_id = @Media_Set_ID EXEC MASTER..xp_fileexist @FilePath , @FileStatus OUT
IF @FileStatus = 1 SET @FileAvailable = 'Available' ELSE SET @FileAvailable = 'NOT Available' IF (DATEDIFF(DAY,@DB_BKPdate,GETDATE()) > 7) SET @Status = 'Warning' ELSE SET @Status = 'Healthy' SET @BackupSize = (@BackupSize/1024)/1024 INSERT INTO #Backup_Details ( ServerName, DatabaseName, BkpDate, BackupSize_in_MB, Status, FilePath, FileAvailable
) SELECT @ServerName, @DBName, @DB_BKPdate, @BackupSize, @Status, @FilePath, @FileAvailable UPDATE #Backup_Details SET status = 'Warning' WHERE bkpdate IS NULL SET @Counter = @Counter - 1 END SELECT * FROM #Backup_Details WHERE databasename NOT IN ('tempdb','northwind','pubs') ORDER BY databasename
DROP TABLE #Backup_Details
SET NOCOUNT OFF GO
|
Full and TXLog Backups with email alert:
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
IF 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] GO
CREATE 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 acceptible 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 ) SELECT T1.Name as DatabaseName, t2.type, MAX(T2.backup_finish_date) as LastBackUpTaken, MAX(T2.user_name)as UserName FROM sys.databases T1 LEFT OUTER JOIN msdb.dbo.backupset T2 ON T2.database_name = T1.name WHERE T1.state_desc = 'ONLINE' AND backup_finish_date > DATEADD(mm, -2, GETDATE()) GROUP BY T1.Name, t2.type ORDER BY T1.Name, t2.type
INSERT #BackupList2 ( DatabaseName, LastFullBackupTaken, LastDiffBackupTaken, LastLogBackupTaken )
SELECT x.DatabaseName, MAX(x.LastFullBackupTaken), MAX(x.LastDiffBackupTaken), MAX(x.LastLogBackupTaken) FROM ( SELECT DatabaseName AS DatabaseName, LastBackupTaken AS LastFullBackupTaken, NULL AS LastDiffBackupTaken, NULL AS LastLogBackupTaken FROM #BackupList1 WHERE BackupType = 'D'
UNION
SELECT DatabaseName AS DatabaseName, NULL AS LastFullBackupTaken, LastBackupTaken AS LastDiffBackupTaken, NULL AS LastLogBackupTaken FROM #BackupList1 WHERE BackupType = 'I'
UNION
SELECT DatabaseName AS DatabaseName, NULL AS LastFullBackupTaken, NULL AS LastDiffBackupTaken, LastBackupTaken AS LastLogBackupTaken FROM #BackupList1 WHERE BackupType = 'L' ) as x GROUP BY x.DatabaseName
-- Identify states 1a and 1b UPDATE #BackupList2 SET FullOverdue = 1 WHERE ( ((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 #BackupList2 SET DiffOverdue = 1 WHERE ( (DATEDIFF(hh, LastDiffBackupTaken, @Now) > 24) )
-- Identify state 3 UPDATE #BackupList2 SET LogOverdue = 1 WHERE ( (DATEDIFF(hh, LastLogBackupTaken, @Now) > 6) )
/* --Some test broken backups update #BackupList2 set fulloverdue = 1 where databasename = 'msdb' update ##BackupList2 set diffoverdue = 1 where databasename = 'msdb' update #BackupList2 set logoverdue = 1 where databasename = 'model' update #BackupList2 set fulloverdue = 1 , logoverdue = 1 where databasename = 'model' update #BackupList2 set fulloverdue = 1 , diffoverdue = 1, logoverdue = 1 where databasename = 'master' */
INSERT ##MailBody(LineText) VALUES ('The following database backups are overdue:') ;
INSERT ##MailBody(LineText) VALUES ('') ;
INSERT ##MailBody(LineText) SELECT CHAR(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 #BackupList2 WHERE 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'<profile name>', @recipients = N'<user or group email>', @subject = N'Daily Backup Status Check ' , @query = @cmd, @query_result_header = 0
END
SELECT * FROM #BackupList1 SELECT * FROM #BackupList2 SELECT * FROM ##MailBody
DROP TABLE #BackupList1 DROP TABLE #BackupList2 DROP TABLE ##MailBody
|
 Updating...
Andy Hughes, Jun 7, 2012, 10:54 AM
Andy Hughes, Jun 7, 2012, 10:54 AM
|