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

Published by Ramasankar

Hi. I’m Ramasankar Molleti. I’m a passionate IT professional with over 14 years of experience on providing solutions for customers who are looking on cloud computing, Database Migration, Development, and Big Data. I love learning new technologies and share my knowledge to community. I am currently working as Sr Cloud Architect with focus on Cloud Infrastructure, Big Data. I work with developers to architect, build, and manage cloud infrastructure, and services. I have deeep knowledge and experience on working with various database platforms such as MS SQL Server, PostgeSQL, Oracle, MongoDB, Redshift, Dyanamodb, Amazon Aurora. I worked as Database Engineer, Database Administrator, BI Developer and successfully transit myself into Cloud Architect with focus on Cloud infranstructure and Big Data. I live in USA and put my thoughts down on this blog. If you want to get in touch with me, contact me on my Linkedin here: https://www.linkedin.com/in/ramasankar-molleti-23b13218/ My Certifications: Amazon: AWS Certified Solutions Architect – Professional AWS Certified DevOps Engineer – Professional certificate AWS Certified Big Data – Specialty AWS Certified Security – Specialty certificate AWS Certified Advanced Networking – Specialty certificate AWS Certified Solutions Architect – Associate Microsoft: Microsoft® Certified Solutions Associate: SQL Server 2012/2014 Microsoft Certified Professional Microsoft® Certified IT Professional: Database Administrator 2008 Microsoft® Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: