Setting up email notifications for your SQL database backup jobs can be a useful way to stay informed about important events and updates. In this guide, we will walk you through the process of configuring database mail and setting up email notifications using SQL Server Management Studio (SSMS).
Before we begin, please make sure you have a properly configured mail server. For this guide, we will be using smtp.sendgrid.net as the SMTP server. For details you may visit : https://sendgrid.com/pricing/
Step 1: Open SQL Server Management Studio (SSMS) and connect to the Database Engine. Expand the “Management” section, right-click on “Database Mail,” and select “Configure Database Mail.”
For details visit : Configuring Database Mail in SQL Server
Step 2: Test the email configuration by right-clicking on “Database Mail” and selecting “Send Test E-Mail.” Enter an email address for testing and click “Send Test E-Mail.”
Step 3: Create a Maintenance Plan to perform full backup of databases.
Step 4: Right-click on “SQL Server Agent” at the bottom & select “Properties.”
Step 5: Under the “Select a page” section, click on “Alert System.”
- Enable the mail profile under the “Mail session” section and ensure the correct profile is selected. Here we have selected Alerts_mail, which was created previously.
- Enable token replacement for all job responses to alerts under “Token replacement.”
Click “OK.”
Step 6: Create an operator by right-clicking on “Operators” under the SQL Server Agent and selecting “New Operator.” Enter the recipient’s email address and click “OK.”
Step 7: Create or modify an SQL Server job. Set up a job, such as a backup job, and configure it to notify on completion. We want the system to send emails for success or failure.
Step 8: Find the newly created maintenance plan under SQL Server Agent. Expand the “Jobs . In the job properties, click on “Notifications,” select “Email,” choose the operator we created from the drop-down menu, and select “When the Job Completes.”
Step 9: Save the changes and close the properties window.
Step 27: Now it’s time to test the setup. Run the job you created, such as the database backup job. Once it completes, you should receive an email notification similar to the one shown.
By following these steps, you can easily set up email notifications for your SQL database, ensuring you stay updated on important events and job statuses. This feature can be particularly helpful for monitoring the success or failure of crucial tasks and taking timely actions.