Find Missing Backups and send email notification

This is another useful script to find missing backups and send email notifications to DBA

IF OBJECT_ID(N'tempdb..#Missing_Backups', N'U') IS NOT NULL
DROP TABLE #Missing_Backups;
GO
create table #Missing_Backups (
[DB Name] [varchar](100) NOT NULL,
[Type] [varchar] (5) NOT NULL,
[Last Backup] [varchar](100) NULL)

insert into #Missing_Backups
SELECT d.name AS "Database",'Full' as "Type",
ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM msdb.dbo.backupset
WHERE type LIKE 'D'
GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1)
AND d.name <> 'tempdb'
AND d.state_desc = 'ONLINE'
UNION
SELECT d.name AS "Database",'Trn' as "Type",
ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Log Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM msdb.dbo.backupset
WHERE type LIKE 'L'
GROUP BY database_name,type) b on d.name=b.database_name
WHERE recovery_model = 1
AND (backupdate IS NULL OR backupdate < getdate()-1)
AND d.name <> 'tempdb'
AND d.state_desc = 'ONLINE'
declare @cnt int
select @cnt=COUNT(1) from #Missing_Backups
if (@cnt > 0)
begin

declare @strsubject varchar(100)
declare @oper_email nvarchar(100)

set @oper_email = (select email_address from msdb.dbo.sysoperators where name = 'DBA')
select @strsubject='Check for missing backups on ' + @@SERVERNAME

declare @tableHTML nvarchar(max);
set @tableHTML =
N'<H3>Databases Missing Backups Listing - ' + @@SERVERNAME +'</H1>' +
N'
<table border="1">' +
N'
<tr>
<th>DB Name</th>
<th>Type</th>
' +
N'
<th>Last Backup</th>
</tr>
' +
CAST ( ( SELECT td = [DB Name], '',
td = [Type], '',
td = [Last Backup]
FROM #Missing_Backups
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>
' ;

EXEC msdb.dbo.sp_send_dbmail
@from_address='',
@recipients= @oper_email,
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name='DBAProfile'

end
IF OBJECT_ID(N'tempdb..#Missing_Backups', N'U') IS NOT NULL
DROP TABLE #Missing_Backups;
go

Cheers

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter