When last we met, we had written a SQL Server Agent job to email us whenever a Microsoft SQL Server database had gone too long without backup.

This post contains a couple of refinements compared to the previous post. We’re able to specify different maximum backup delays for log and full backups. And this job automatically checks every database in the SQL Server instance; the job in the previous post checked only those on a list that we provided.

A happy chef

You might prefer this version with the refinements, or you might prefer the earlier simpler version, depending on your application.

Here’s the more automated later version.

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.sys.databases s
join msdb.dbo.backupset b
ON s.name = b.database_name
-----------------------------------------------------------------
-- Here are the alarm threshholds, identified as HoursBeforeAlarm 
--   for each type of backup (full and log)
-----------------------------------------------------------------
join (select 'D' as BackupKey, 'FULL' as BackupType, 24 as HoursBeforeAlarm 
  union select 'L', 'LOG', 5) t
on b.type=t.BackupKey
where s.recovery_model in (1)  -- Either full recovery mode
or b.type <> 'L'  -- or not the log backup 
-- (skip log backups for simple recovery mode)
group by s.name, t.BackupType, t.HoursBeforeAlarm
having coalesce(datediff(minute,max(b.backup_start_date),current_timestamp), 
  99999999) / 60.0 > t.HoursBeforeAlarm
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='recipient1@yourcompany.com;recipient2@yourcompany.com', @subject='Database backup warning', @body_format='HTML', @body=@buf ; end;

Hope this is helpful!

Leave a Reply

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

 characters available