Using XP_CMDSHELL we can run commands that could otherwise be run using Windows Scheduled Tasks. It is kick ass, what makes this even more kick ass is being able to capture the errors running these commands produce.

Below is an example of a simple script which runs a XP_CMDSHELL command and sends an email in the event of any failure. Dare I say this is even quick and dirty . . .

use msdb

declare @cmd_to_run varchar (1500)
declare @return_code int

--The command you want to run
select @cmd_to_run = '<<INSERT YOUR OWN COMMAND>>'

-- Run the command, capture what int code is returned
exec @return_code = master.dbo .xp_cmdshell @cmd_to_run

-- Returning a 0 means there is a problem, send an email if 0 is returned 
if @return_code <> 0
       print 'FAILED'
       exec sp_send_dbmail
            @profile_name = '<<PROFILE NAME>>' ,
            @recipients = '<<RECIPIENTS>>' ,
            @importance = 'high' , --somethings failed dudes, its always high importance
            @subject = '<<SOMETHING MEANINGFUL TO YOU>>' ,
            @body = ''<<SOMETHING MEANINGFUL TO YOU>>'



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
                               set @failureCount = 0


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

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

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

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

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

       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


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'


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.