Create User Friendly Backup Failure Alter Email

At my current work, daily database backup is one of many steps in a SQL Agent Job. Because of business requirement, the job is required to continue if one or many steps fail.  However it would cause problems…The main issue in the past was that DBA wouldn’t be informed if the backup job failed, as the job would go on the next step on the failure action. Furthermore, when we re-run the backup job after failure, it is difficult to tell which databases have been backed up and which databaes are left, and we don’t want to multi backup the same databases.

Therefore, my solution is to use ‘sp_send_dbmail’ and ‘RAISERROR’ inside the Catch block of a TRY…CATCH construct.

/*===================================================================

*** First Part          

*** Back up all databases

***

===================================================================*/

BEGIN TRY

/*-------------------------------------------------------------------

1. Create temp table to list all the database that you want to backup

Here, it is going to backup all the databases except of tempdb

---------------------------------------------------------------------*/

   IF Object_id('tempdb..#BackupList') IS NOT NULL

       DROP TABLE #BackupList 

   SELECT   IDENTITY(tinyint,1,1) AS BUDBID

          , [name] AS BUDBName

          , 'N'                  flag

   INTO     #BackupList

   FROM     master.sys.databases

   WHERE    [name] NOT IN ('tempdb')

   ORDER BY [name]

-- select * from #BackupList

/*-------------------------------------------------------------------

2. Backup all the databases in #Backuplist

   by looping through all the databases on #BackupList

-------------------------------------------------------------------*/

 DECLARE  @ID tinyint,

          @Path varchar(255),

          @strSQL nvarchar(1000),

          @ServerName varchar(255),

          @DatabaseName varchar(255)

         

         

   SET      @Path='\\filer01\allshares\backups\'

   SET      @ID = 1

   SELECT   @ServerName = @@SERVERNAME

 

   WHILE (@ID) <= (SELECT MAX(BUDBID) FROM #BackupList)

         BEGIN

               SELECT   @DatabaseName=BUDBName

               FROM     #BackupList

               WHERE    BUDBID=@ID

               SET      @strSQL = N'BACKUP DATABASE '+ @DatabaseName +' TO DISK='''+@Path+@ServerName + '.Full_' + @DatabaseName +'.SQL.BAK'' WITH INIT'

    

               PRINT    'Starting Backup Of: '+ @DatabaseName +' at: ' + CONVERT(varchar,GETDATE(),120) + ' using: ' + @strSQL

              

               EXECUTE  master.dbo.sp_executesql @strSQL      

              

               PRINT    'Completed Backup Of: '+ @DatabaseName +' at: ' + CONVERT(varchar,GETDATE(),120)

     

 -- Reset flag to 'Y' after backup is completed

               UPDATE #BackupList

                  SET    flag = 'Y'

               WHERE  BUDBID=@ID

               SET    @ID=@ID+1

                

         END

 END TRY

/*===================================================================

*** Second Part          

*** Error Handling

***

===================================================================*/

 BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);

    DECLARE @ErrorNumber CHAR(5);

    DECLARE @ErrorSeverity CHAR(2);

    DECLARE @ErrorState CHAR(2);

    DECLARE @BodyText NVARCHAR(4000);

    DECLARE @XML_Table NVARCHAR (4000);

   

    SELECT   @ErrorMessage = ERROR_MESSAGE()

    SELECT   @ErrorNumber = CAST(ERROR_NUMBER()  AS CHAR(5))

    SELECT   @ErrorSeverity = CAST(ERROR_SEVERITY() AS CHAR(2))

    SELECT   @ErrorState = CAST(ERROR_STATE() AS CHAR(2))

/* ------------------------------------------------------------------

 Create Job failure Email format

 ------------------------------------------------------------------*/

-- In email content, it tells you

-- 1. The name of the backup job

-- 2. The databases that the job failed on

-- 3. The brief error message

    SELECT   @BodyText = N'Database Full Backup step failed in SQL Agent job ''JOB NAME''!<br><br>        

                        Unable to backup database ''' + @DatabaseName + '''. Please the error message:<br><br>    

                        ''Msg '+@ErrorNumber+', Level '+@ErrorSeverity+', State '

                      + @ErrorState + @ErrorMessage + ''''

                      + '<br><br>Please check backup table, and run the backup job for the databases that have NOT been backed up.'

                   

-- 4. Which databases have been backed up and which haven't been backed up

 

    SELECT @XML_Table = N'<br><br><table border=1 cellpadding=2>'

                      + N'<tr><th>DB Name</th><th>Flag</th></tr>'

                      + CAST ( ( SELECT td = BUDBName, '',                    

                                          td = flag, '' 

                                     FROM #BackupList

                         FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) )

                      + N'</table><br><br>'

   

    SELECT @Bodytext = @Bodytext + @XML_Table

 /* -----------------------------------------------------------------

   Send failure notification email to DBA team   

 ------------------------------------------------------------------*/

    EXEC msdb.dbo.sp_send_dbmail

     @recipients = 'ITDBA@company.com',

     @body = @BodyText,

     @subject = 'Database Full Backup Failed' ,

     @importance= 'High',

     @body_format = 'HTML';

/* ------------------------------------------------------------------

   Raise Error in Error Log

-------------------------------------------------------------------*/

    

     RAISERROR (@ErrorMessage, -- Message text.

                @ErrorSeverity, -- Severity.

                @ErrorState -- State.

                );

  

      PRINT  @BodyText

 END CATCH