How To Configure MS SQL Server to send an email if a SQL Server (Backup) Job fails

Here is a great article on how to setup and configure your MS SQL Server to send you an email if your nightly backup fails.
It isn’t difficult, but if you miss one step, it won’t work. Ugh!! It requires that you know your mail servers SMTP settings for sending email.
If you don’t know this you should contact your  Mail Server Administrator first to get those settings.

1. Configure Database Mail.

2. Create a SQL Server job.

3. Adjust the properties within the SQL Server Agent.

4. Create an Operator.

5. Adjust the SQL Server job to send on Failure.

Configuring Database Mail

To complete this step correctly, a properly configured SMTP mail server is needed. In most cases it is okay to use localhost, but that requires the installation of Microsoft IIS/SMTP. If your administrator will not allow the install of IIS on a server running SQL Server, get the name and settings of an SMTP mail server that can be used.

Step. 1
Open SQL Server Management Studio (SSMS), connect to the Database Engine, expand Management, right-click on Database Mail and then click Configure Database Mail.

clip_image002_thumb1

Step. 2
You will then see the splash screen below. Click Next.

clip_image004_thumb1

Step. 3
Leave the default option of “Set up Database Mail by performing the following tasks” selected. Click Next.

clip_image006_thumb1

Step. 4
You will get a pop-up below if mail hasn’t been configured yet. Click Yes and then click Next.

clip_image008_thumb1

Step. 5
Give the New Profile a name and then click Add.

clip_image010_thumb1

Step.6
Fill in the correct information as show below.

· Email address– This should be the senders email address.

· Display name– This should be the name displayed when the email arrives.

· Reply email– This should be set properly too.

· Server name– The mail server address. In this case, I am using localhost (127.0.0.1), but you will want to use your SMTP mail server name if you do not have IIS installed.

· SMTP Authentication– I leave this section set to the default, but you can specify it to your needs if it is required.

Note that the OK button will remain grayed out until all required fields are completed. Once all the fields are completed, click OK.

clip_image012_thumb1

Step. 7
Click Next.

clip_image013_thumb1

Step. 8
Check the option to make the new profile Public. If the profile isn’t set to Public, permissions will need to be assigned properly. I am not going to discuss this here, but it will be important to note the difference.

clip_image015_thumb1

Step. 9
Review the System Parameters. I always use the default settings, but they can be adjusted as needed. You can review the settings in-depth. Click Next.

clip_image017_thumb1

Step. 10
Click Finish.

clip_image019_thumb1

Step. 11
You should see that each Action was completed and has a Status of Success. Click Close.

clip_image021_thumb1

 

Now let’s test.

Right-click Database Mail and select Send Test E-Mail… type in an email address to use for testing and click Send Test E-Mail.

clip_image022_thumb1

You will then see the screen below.

clip_image024_thumb1

If the email doesn’t arrive, check the mail server to make sure it is properly configured and will accept mail from the SQL Server.


Create SQL Server Job

At this point we know the SQL Server can send email. Now we need to set up a SQL Job and then set SQL so it sends emails if the job fails. The easiest way to test this is to set up a Backup job and set it to notify on “Success”.  Once it is working properly, change the job to notify on “Failure”. In most cases you might already have a Backup Job / Maintenance Plan setup, in this case go to the next section.

To setup a simple Backup job, see instructions here.

Note: The Maintenance Plan Wizard can also be used to set up a backup job very quickly.


Adjust the properties within the SQL Server Agent

Right-click SQL Server Agent and select Properties.

clip_image026_thumb1

Click on Alert System under Select a page.

· Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profile is selected.

· Under Token replacement, enable Replace tokens for all job responses to alerts.

Click OK. Restart the SQL Server Agent service.

clip_image028_thumb1


Create an Operator

Under the SQL Server Agent, right-click Operators and select New Operator…

clip_image030_thumb1

Type in the recipient email address in the E-mail name and click OK.

clip_image032_thumb1


Adjust the SQL Server job to send the email on Success (for testing purposes only).

Expand SQL Server Agent and click on Jobs. Find the job you created above, right-click it and select Properties.

Under Select a page, select Notifications. Enable the first option, E-mail. Then select the Operator we just created and change the job to email When the job succeeds (for testing purposes only). Click OK.

clip_image034_thumb1

That’s it!! Now we test. Run the job you created that will backup databases. You should receive an email similar to the one below once it completes:

If an email isn’t received, restart the SQL Server Agent once more and then test again. If everything works, just go back and change it for “When the Job Fails”  vs  “When the Job Succeeds”

Feel free to leave a comment with any questions, concerns or thoughts on the above.