Notify if a database backup missing

declare @ServerName varchar(250)

declare @DatabaseList varchar(4000)

declare @CountMissingBackups int

declare @MailSubject varchar(250)

declare @MailText varchar (4000)

 

;WITH CTE as

(

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],

   msdb.dbo.backupset.database_name

FROM    msdb.dbo.backupset

WHERE     msdb.dbo.backupset.type = 'D'

GROUP BY msdb.dbo.backupset.database_name

HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

 

UNION

 

--Databases without any backup history

SELECT    

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],

   master.dbo.sysdatabases.NAME AS database_name

FROM

   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset

       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name

WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'

--ORDER BY msdb.dbo.backupset.database_name

)

select @CountMissingBackups = count(*), @ServerName = [Server] ,@Databaselist = COALESCE(@DatabaseList + ', ', '') + CAST(database_name AS varchar(250))

from CTE

group by [Server], database_name

 

 

-- send the mail

 

if @CountMissingBackups > 0

begin

 

set @MailSubject = 'Backups on '+@ServerName

set @MailText = 'The following databases have not been backed up in the past 24 hours: '+@Databaselist

 

EXEC msdb.dbo.sp_send_dbmail 

@profile_name='Default',

@recipients='abc@XYZcom',

@subject=@MailSubject,

@body=@MailText

 

end