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.
3. Adjust the properties within the SQL Server Agent.
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.
Step. 2
You will then see the splash screen below. Click Next.
Step. 3
Leave the default option of “Set up Database Mail by performing the following tasks” selected. Click Next.
Step. 4
You will get a pop-up below if mail hasn’t been configured yet. Click Yes and then click Next.
Step. 5
Give the New Profile a name and then click Add.
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.
Step. 7
Click Next.
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.
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.
Step. 10
Click Finish.
Step. 11
You should see that each Action was completed and has a Status of Success. Click Close.
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.
You will then see the screen below.
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.
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.
Create an Operator
Under the SQL Server Agent, right-click Operators and select New Operator…
Type in the recipient email address in the E-mail name and click OK.
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.
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.
