Setup SQL Server 2008 Maintenance Plan Email Notifications

For most of the SQL installs that I maintain, nightly SQL dumps to disk and then copy to tape is my preferred backup method. I use a simple maintenance plan that dumps all user databases to the local disk and then a cleanup task that purges backup files older than a set number of days. An email alert with either success or fail in the subject line is sent out after each maintenance plan task is completed. This article will review step by step how to add email notifications to your existing SQL 2008 maintenance plan.

First step is to configure Database Mail. Open Microsoft SQL Server Management Studio then right-click on Database Mail > select Configure Database Mail

Skip the welcome screen and select Next on the Select Configuration Task window.

Create new profile > fill out Profile name > Select Add under SMTP accounts:

Fill out New Database Mail Account info:

Configure Profile Security > check Public > set as Default > Next > Finish > close

Send test email. Right-click on Database Mail

Fill out test info, select Send Test Email. 

Check inbox, select OK on the confirmation screen. If you dont recieve test email then double check and verify smtp settings.

Next step is to configure Operators. Under Object Explorer right-click on Operators > New Operator

Fill out New Operator info (minimum name and email address)

Select OK.

Next, right click on designated maintenance plan (assuming one is already configured) and select Modify

This should bring up the design window with the current tasks

From the Toolbox window Drag and drop Notify Operator Task to Design window twice. One for success and the other for fail.

Connect the backup database task to each Notify Operator Task and make sure the arrows are pointing down.

Designate one of the Notify Operator Task objects connection arrows as Failure. Right click on connection and select Failure. This will turn the connection arrow red.

Double click each Notify Operator Task > check which operators to notify if there are more than one > fill out Subject and Body fields > select OK

Save Maintenance Plan and test.

One of the nice features of the Notify Operator Task in SQL 2008 that wasn't an option in SQL 2005 is the ability to add a unique subject line to the message. Its helpful to be able to see the success or fail status at a glance from just the subject line especially with the morning barrage of emails. Comments or questions welcomed.