Add the below script to step of backup job to get an alert
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H2>Last Backup Report For</H2>' +
N'<H3>'+ (select td = (select @@servername)) +'</H3>'+
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Days Since Last Backup</th>' +
N'<th>Last Backup Date</th>' +
CAST ( ( SELECT td = msdb.dbo.backupset.database_name, '',
td = datediff(day, max(msdb.dbo.backupset.backup_finish_date), getdate()), '',
td = CONVERT(VARCHAR(20), max(msdb.dbo.backupset.backup_finish_date), 100)
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb.dbo.backupset.database_name IN (SELECT name FROM master.sys.databases)
GROUP BY msdb.dbo.backupset.database_name
ORDER BY datediff(day, max(msdb.dbo.backupset.backup_finish_date), getdate()) DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Project',
@recipients='Email Id',
@subject = ' Production Server Backup Report',
@body = @tableHTML,
@body_format = 'HTML' ;
