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.

You know the score. You set up a maintenance plan to backup all or most of your databases on an instance then you get an email saying the job has failed. Doesn’t mean they all didn’t backed up . . . Get a list of the last backup for every database in your instance, backup only the one which failed to backup.

Change the type of backup to ‘D’ to get the last full and ‘I’ to get the last differential

select bs. database_name, bs .type, max( bs. backup_finish_date ) AS Latest_bak
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, type
order by database_name

Use this T-SQL to find this out and kick ’em off 😉 !

select name
from master.. syslogins 
where sysadmin = '1'