Our third party backup software occasionally errors out.

A worried person

Oh no!

We’ve developed a SQL Server Agent job to make sure we never go longer than 5 hours without a backup. If this job finds any databases have gone longer than 5 hours without a full or log backup, it sends an email with the details.

declare @dbname varchar(max);
declare @buf varchar(max);
declare @latestbackup datetime;
declare @backuptype varchar(20);

set @buf = '';

declare late_backup_cursor cursor for
select 
  s.name as DBName,
  coalesce(max(b.backup_start_date), '1900-01-01') as LatestBackup,
  t.BackupType
from master..sysdatabases s
join msdb..backupset b
ON s.name = b.database_name
join (select 'D' as BackupKey, 'FULL' as BackupType 
  union select 'L', 'LOG') t
on b.type=t.BackupKey
where s.name in ('YourDatabase1','YourDatabase2','YourDatabase3')
group by s.name, t.BackupType
having coalesce(datediff(minute,max(b.backup_start_date),current_timestamp), 
  99999999) / 60.0 > 5
order by s.name, t.BackupType
;

open late_backup_cursor;

fetch next from late_backup_cursor into @dbname, @latestbackup, @backuptype;

WHILE @@FETCH_STATUS = 0
  begin
  set @buf += 'There has been no ' + @backuptype + '  backup for ' + @dbname + 
    ' since ' + convert(char(19), @latestbackup, 121) + '
'; fetch next from late_backup_cursor into @dbname, @latestbackup, @backuptype; end; close late_backup_cursor; deallocate late_backup_cursor; if @buf <> '' begin set @buf = '' + @buf + ''; EXEC msdb.dbo.sp_send_dbmail @profile_name='YourDatabaseMailProfile', @recipients='person1@yourcompany.com;person2@yourcomnpany.com', @subject='Database backup warning', @body_format='HTML', @body=@buf ; end;

This is a pretty basic script but maybe (I hope) it will help someone else in a similar situation.

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available