The script below will generate an email if a database has had a full backup in the last 7 days, listing the database and the last backup in a table. This could be useful to flag up databases which failed to be backed up during a job or just for auditing/informational purposes. This script is attached to a job and is scheduled to run every after the full backup maintenance plan has run to inform the SQL team of any databases missing a bak.

-- any full backup failures?
declare @failureCount         int
set @failureCount =  1

if exists (

select null
from sys .databases as d
left join msdb.dbo.backupset as bs
on d. name = bs .database_name
where is_copy_only = 0 and type in ('D' )
group by database_name
having      datediff (D, max( BACKUP_FINISH_DATE), getdate()) > 7
)
                         begin
                               set @failureCount = 0

                         end

--if a full backup is missing, run the following 
if (@failureCount = 0)
begin

declare @bodyText              nvarchar(max )
declare @bodyLine              nvarchar(512 )

declare @databaseName         nvarchar( 128)
declare @@lastbak             nvarchar(128 )

-- populate mail body top
print 'populate table'
set @bodyText = N'<html><body>'
set @bodyText = @bodyText + N'<style> type="text/css" body {font-family:"Calibri";font-size:"10pt";} </style>'
set @bodyText = @bodyText + N'The following databases did not get a full backup on %s - verify is this is correct:'
set @bodyText = @bodyText + N'<br><br><table border="1">'
set @bodyText = @bodyText + N'<tr><th>Database Name</th><th>Last Backup</th></tr>'
set @bodyText = replace( @bodyText,N'%s' ,@@servername)

-- cursor for disabled jobs
print 'starting cursor'

declare csrDatabases cursor fast_forward
for

select
       cast(bs . database_name as nvarchar( 128)) ,
       cast(max ( bs. backup_finish_date ) as nvarchar (128))
from
       sys . databases as d
       left join msdb. dbo.backupset as bs
on
      d . name = bs .database_name
where
      is_copy_only = 0 and type = 'D'
group by
      database_name
having
       datediff(D , max (BACKUP_FINISH_DATE), getdate()) > 7
order by
      database_name

open csrDatabases 
fetch next from csrDatabases
into @databaseName, @@lastbak

-- populate mail html table with disabled jobs
while @@fetch_status <> -1
begin

       set @bodyLine = N'<tr><td>%1</td><td>%2</td></tr>'
       set @bodyLine = replace( @bodyLine,N'%1' ,@databaseName)
       set @bodyLine = replace( @bodyLine,N'%2' ,@lastbak)
       set @bodyText = @bodyText + @bodyLine

       fetch next from csrDatabases
       into @databaseName, @@lastbak

end

close csrDatabases 
deallocate csrDatabases 
print 'cursor ended'

-- populate mail body bottom
set @bodyText = @bodyText + N'</table>'
set @bodyText = @bodyText + N'</body></html>'

-- send mail
print 'start sending mail'
exec sp_send_dbmail
       @profile_name = 'profile_name',
      @recipients = 'email_address' ,
      @importance = 'high' ,
      @subject = 'BACKUP - FULL - MISSING ,
      @body  = @bodyText,
      @body_format = 'html'
print 'finish sending mail'
end

go

Note, with regard to backup ‘type’

  • ‘D’ denotes a full backup
  • ‘I’ denote a differential
  • ‘L’ denotes a log backup

This script could be easily adapted to check differential’s and log files.