Setup SQL Server 2008 Maintenance Plan Email Notifications

To take backup most of the SQL installs that I maintain, SQL dumps to disk and then copy to tape. 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. Here we will review step by step how to add email notifications to your existing SQL 2008 maintenance plan.


Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert. So how do you send an alert to an operator?
Step: 1
Database Mail: First setup database mail with a profile named "TestDb/SQLAlerts". The profile can be named anything but in these instructions, the profile name SQLAlerts is referenced. If you wish to use a different profile name just substitute accordingly. For procedures in setting up database mail, see this Post First
Step: 2
Define Operator : Connect to the instance using Microsoft SQL Management Studio
Double Click SQL Server Agent-->Right Click on Operators and select New Operator

Specify an operator Name, E-mail name

Select Ok button. New operator will be create.

Step: 3

Setup SQL Agent Settings
Note: This step is often overlooked when creating an alert for the first time. SQL Agent must be setup correctly for operators to receive an alert e-mail.
Right Click SQL Server Agent > select Properties

Popup window will display select Alert System in the left pane then follow below steps.
Checkmark > Enable mail profile
Verify Mail system: Database Mail
Verify Mail Profile: TestDB
Checkmark > Include body of e-mail in the notification message
Click OK.

Restart SQL Agent to activate settings.
Warning: Restarting SQL Agent will cancel any executing jobs.

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. Notify Operator Task, it shows cross mark.  

We need to configure to that to do that 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

Design window with the current tasks will look like this.

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.

Save maintenance plan and test it.

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.