https://www.sikich.com

SQL Backup Notifications – The Next Step in Data Security

James Rodrigues

|

Apr 29 2019

|

3 min read

Data integrity and protection are two of the most important tenets of using SQL databases. In most cases, IT will set up daily backups and weekly maintenance tasks to ensure these two tenets are observed. Yet, unless you dedicate one person to monitor every SQL agent job execution there could be a scenario where either the backup jobs or maintenance jobs fail. The worst case scenario is that you need to restore your GP databases after a catastrophic SQL failure and the most recent database backup is months old.

This is where SQL Database Mail can come to the rescue. SQL Database Mail allows SQL to send out emails based on conditions or delineated tasks. In other words, you can set up email notifications for SQL backup failures.

In order to setup Database Mail you will need to follow these simple steps (example uses SQL 2008R2):

Launch SQL Server Management Studio.

Expand the Management node.

Right-click Database Mail, click Configure Database Mail.

configure database mail

Click Next.

Database Mail configuration wizard

Select Set up Database Mail by performing the following tasks, click Next.

select configuration task

If Database Mail is not currently enabled, then you will be prompted to enable it, click Yes.

SQL server management studio

Click Add.

how to set up SQL email notifications

Enter in an appropriate account name and description. Enter in the email address you want to send the notifications from and the email server that you want to use. If the port number is not
the default port 25 then change it. If you don’t use Anonymous Authentication then choose the appropriate authentication method and credentials. Also, check off the SSL option if the email server requires a secure connection. Once you are done, click OK.

how to set up SQL email notifications

Enter an appropriate profile name and description, click Next.

how to set up SQL email notifications

Check the Public box and select Yes in the Default Profile drop-down, click Next.

how to set up SQL email notifications

Check the system parameters, click Next.

how to set up SQL email notifications

Check the synopsis, click Finish.

how to set up SQL email notifications

Once Database Mail has been configured, right-click Database Mail, click Send Test E-Mail…

how to set up SQL email notifications

You will be prompted to enter an email to send the test email to. Confirm that the email is received by the recipients.

Now that Database Mail has been configured, it is time to create the Operators in SQL.

Expand the SQL Server Agent node, right-click Operators, click New Operator.

how to set up SQL email notifications

Enter an appropriate operator name and email address, click OK.

how to set up SQL email notifications

That’s it! You can now add a Notify Operator task to any SQL Maintenance plan which will ensure that you are kept apprised of any issues with the database (backups/integrity/etc.).

Have any questions about setting email notifications for SQL backup failures? Don’t hesitate to reach out at any time!

This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.

About the Author

James Rodrigues

Senior Technical Consultant at Sikich. Proficient in SQL and GP/CRM technical aspects. Project Manager for the GP Upgrade Team and Technical Projects.