SQL Backup Notifications – The Next Step in Data Security

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 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.

how to set up SQL email notifications

Click Next.

how to set up SQL email notifications

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

how to set up SQL email notifications

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

how to set up SQL email notifications

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? Don’t hesitate to reach out at any time!

By |2019-04-29T05:19:52+00:00April 29th, 2019|Security, Technology|0 Comments

About the Author:

Senior Technical Consultant at Sikich. Proficient in SQL and GP/CRM technical aspects. Project Manager for the GP Upgrade Team and Technical Projects.
This material has been prepared for general informational purposes only and is not intended to be relied upon as accounting, tax, or other professional advice. Please refer to your advisors for specific advice.

Privacy Information

We use cookies to personalize the website for you and to analyze the use of our website. Click 'Privacy Options' to configure how we can interact with you and your device or computer.

Privacy policy | Close
Settings
private equity services construction accountants Agriculture Services Construction Services Non-Profit Services Government Services