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
begin
       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>>'
end

 

 

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.