Send email notification if databases are not backed up in specific duration.

by Joe Havelick 10. May 2010 14:15

Backup utilities may notify you when a backup fails, but don't provide coverage for you forgetting to set the job, or not having permissions to a certain resource. As part of my protection plan, in addition to regular notifications, I like to know if a database HAS NOT been backed up for any reason within the past two days.  The following script accomplishes this.

Note that this utilizes Database Mail and a DBMail profile called "Default DB Mail Profile". These must be manually configured.  More information can be found here.

 --Declarations 
DECLARE @Threshold DATE = CONVERT(DATE, Getdate()-2) --The cutoff from when we consider backups to be current (2 days before the end of today)
DECLARE @SendEmail BIT = 0
DECLARE @emailbody VARCHAR(2000) = 'The following databases have not been backed since before ' + CONVERT(VARCHAR(32), @Threshold) + ':
<BR/><UL>'
DECLARE
 @DatabaseName VARCHAR(256)
DECLARE
 @LastBackupTaken VARCHAR(256)

DECLARE @UserName VARCHAR(256)
 
DECLARE emailcursor CURSOR FAST_FORWARD FOR
  SELECT t1.name                                                              AS databasename,
         Coalesce(CONVERT(DATE, MAX(t2.backup_finish_date)), 'Not Yet Taken') AS lastbackuptaken,
         Coalesce(CONVERT(VARCHAR(32), MAX(t2.user_name), 101), 'NA')         AS username
  FROM   sys.sysdatabases t1
         LEFT OUTER JOIN msdb.dbo.backupset t2
           ON t2.database_name = t1.name
  WHERE  database_name NOT IN ( 'TempDB' )
  GROUP  BY t1.name
  HAVING Coalesce(CONVERT(VARCHAR(32), MAX(t2.backup_finish_date), 101), 'Not Yet Taken') < @Threshold
  ORDER  BY t1.name

--Open Cursor
OPEN emailcursor
FETCH NEXT FROM emailcursor INTO @DatabaseName, @LastBackupTaken, @UserName
WHILE @@FETCH_STATUS = 0
  BEGIN
      ---
      SELECT @SendEmail = 1
      SELECT @emailbody = @emailbody + '<LI>' + @DatabaseName + ' (' + @LastBackupTaken + ')</LI>'
      FETCH NEXT FROM emailcursor INTO @DatabaseName, @LastBackupTaken, @UserName
      ---    
  END
CLOSE emailcursor
DEALLOCATE emailcursor

SELECT @emailbody = @emailbody + '</UL>'

IF @SendEmail = 1
  BEGIN
      EXEC msdb.dbo.Sp_send_dbmail
        @recipients= 'myemail@address.com',
        @copy_recipients = '',
        @subject = 'SERVER Database Backups',
        @body = @emailbody,
        @body_format = 'HTML',
        @profile_name = 'Default DB Mail Profile';
  END
GO
 

Tags:

Tech Tips

Comments are closed

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations