SQL SERVER BACKUP VERIFICATION EMAILER

Today I wanted to consolidate the half dozen emails I get each morning regarding backups that have failed. With multiple data centers I set up a verification process that inserts data into my SQL PERFORMANCE MONITOR SOLUTION (which I demonstrated a few months ago.)

Using that solution I will create a stored procedure to pull in the data for the multiple datacenters and send me a single email listing everything that has failed. I will then schedule this as an SQL Agent job to run each morning.

My STORED PROC has four parts.
A. I will pull the data I want into a temp table variable.
B. I will set up some fancy HTML code for a style sheet, header and banner for my email.
C. I will format my data into an organized table.
D. I will send the email — only if there were failures.

Here is what I came up with.

CREATE OR ALTER PROC dbo.BackupCheckEmailer
@E_profile_name nvarchar(250)='DBMAILPROFILE',
@E_recipients nvarchar(2000)='ME@MYCOMPANY.com'
AS

BEGIN

-- This PROC will pull backup data from our performance monitor solution data warehouse. It will send an email if there are any failures.
-- This assumes the monitoring solution is working and has not gone down.
-- By David Speight 20230202

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
DECLARE @body1 nvarchar(max);
DECLARE @bodyFIN nvarchar(max);
DECLARE @StyleSheet VARCHAR(1000);
DECLARE @Header VARCHAR(400);
DECLARE @GreenTitle VARCHAR(250);
DECLARE @tData AS TABLE (LocationID int, ClusterID int, ClusterName varchar(250), LocName varchar(50), DbName varchar(250), bStatus int);
	
-- PART [A] Pull the data I want to use into a temp table.
; WITH cteData AS (
SELECT	LocationID, ClusterID, PartName, MIN(iVal) as iVal, MAX(TimeKey) as TimeKey
FROM	Analysis.PerfCounterStats  WITH(NOLOCK)
WHERE	DateKey = CONVERT(VARCHAR(10), GETDATE(),112) and MetricID = 101
GROUP BY LocationID, ClusterID, PartName)

INSERT INTO @tData
SELECT
	B.LocationID,
	B.ClusterID,
	L.ClusterName, 
	RIGHT(L.LocationName,9) AS LocName,
	B.PartName AS DbName,
	B.iVAL  AS bStatus
FROM	Analysis.PerfLocation L
INNER JOIN cteData B ON L.LocationID = B.LocationID and L.ClusterID = B.ClusterID
WHERE	L.IsActive = 1
	AND (B.PartName NOT IN ('master','msdb','model','tempdb','SSISDB','rdsadmin','rdsadmin_ReportServer','rdsadmin_ReportServerTempDB'))
	AND B.iVAL = 0 -- Only get FAILED BACKUPS
GROUP BY B.LocationID, B.ClusterID, L.Clustername, RIGHT(L.LocationName,9), B.PartName, B.iVal
ORDER BY B.LocationID, B.ClusterID, L.Clustername, RIGHT(L.LocationName,9), B.PartName, B.iVal;

-- PART [B]  Set up style sheets and header for the 
SELECT @StyleSheet = 
'<style><!--
@font-face
{font-family:"Microsoft Sans Serif";
panose-1:2 11 6 4 2 2 2 2 2 4;}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
--></style>',
@Header = CONCAT('<p class="MsoNormal" style="background:white"><span style="font-size:16pt;color:#009A44;letter-spacing:-1.0pt">COMPANY</span><span style="font-size:16pt;color:#0C2340">NAME</span></p><BR /><span style="font-size:16pt;color:#0C2340"><p><span style="font-size:16pt;color:#0C2340">BACKUP FAILURE REPORT ON ',CONVERT(varchar(12),getdate()),' </span></p>'),
@GreenTitle = '<p class="MsoNormal" style="background:white"><span style="font-size:16.0pt;font-family:&amp;quot;color:#009A44;letter-spacing:-1.0pt">';

-- PART [C] Display the data in the temp table in a formatted table.
SET @body1 = cast( (
SELECT td = d.ClusterName + '</td><td>' + d.Region + '</td><td>' + d.DbName + '</td><td>' 
FROM (
	SELECT ClusterName, LocName as Region, DbName
	FROM @tData) as d
FOR XML PATH( 'tr' ), type ) as VARCHAR(max) )
set @body1 = '<table cellpadding="2" cellspacing="2" border="0">'
		+ '<tr><th style=''background:#C6EFCE''>Cluster Name</th><th style=''background:#C6EFCE''>Region</th><th style=''background:#C6EFCE''>Database Name</th></tr>'
		+ replace( replace( @body1, '&lt;', '<' ), '&gt;', '>' )
		+ '</table>';
SELECT @body1 = LEFT(@body1,40000); -- Limit to 40 of the 64k

SELECT @bodyFIN  = CONCAT( @StyleSheet,@Header,'<BR />',@GreenTitle,'BACKUP FAILURES</span></p><BR />', @body1,'<BR /><BR />');
SELECT @bodyFIN  = LEFT(@bodyFIN,60000); -- char limit 64k, leaving space for other vars

-- PART [D] Send the email
IF ((SELECT COUNT(*) FROM @tData) > 0)
BEGIN
DECLARE @SubjectTxt  VARCHAR(50) =' Backup Failure Report ';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @E_profile_name,
@recipients = @E_recipients,
@body = @bodyFIN ,
@subject = @SubjectTxt,
@body_format = 'HTML'; 

END;

END;
GO