MSSQL: How to setup email notifications for the SQL server backups jobs


How to setup email notifications for the SQL server backup:

1. Open SQL Management console and connect to the database server  using windows credentials.

2. Click on Management and select Database Mail and Right+Click and Select Configure Database Mail.

 

3. Click Next and Select Configuration Task  select Set up Database Mail by performing the following tasks:
  

4. Create a New Profile  Specify the profile name and description.
 

5. Click Add in SMTP accounts. 

6. Give any Account  name and Provide the E-mail Address from which account you want to send the emails.

7. Give any Display name to identify the Job

8. Provide the SMPT Server name and specify the port (Ex: 127.0.0.1 for local loop back address as I am using my SQL server as SMTP server)

9. Click Ok, and Click Next.



















10. Select the profile SQL Job Notification as Public and Select Default Profile as Yes

 

11. Click Next.

12. Select Database Mail Executable Minimum Lifetime (Seconds) and change 600 to 1740 this value has been identified based on so much research and best practices.
 

13. Click Next and Click Finish. 

14. Check for the Success status in configuring page.
 

15. Click Close. 

16. So we have completed with the mail setup with SQL database and we have to enable those notifications.

17. Right+Click on the SQL Server Agent. Select Properties and Select Alert System and select Enable mail profile 

18. Select Mail System as Database Mail and Mail Profile as  SQL Job Notification which we created just in before steps while selecting Mail Profile if you have multiple profile you can see them in the drop box.

19. Click OK. 

20. Now we have to create an Operator (Receipent).

21. Select SQL Server Agent. 

22. Right+Click, New and Select Operator 

23. Profile a Name (Ex: SQL Job Complete)

24. Provide an email in E-mail name (Ex:  notify-bakcup@domain.com)

25. Click OK.

 






















26. Now we have to enable these notifications in the Jobs which we have created.

27. Go to SQL Server Agent and Select Jobs and Select Full_Backup and Right+Click and Properties.

 

28. Select E-mail and Select the SQL Job Complete from  drop down list just we created.
 

29. Click OK. 

30. Select the event you want to when the Job Completes and Click OK 

31. We can do the same for the Incremental and Log Backups Jobs too.

32. Once we had enabled the Notification, Right+Click on SQL Server Agent and Click Restart so that modification which we made will apply to the server.

 Keywords: email notification, SQL email notifications, MSSQL Backup notifications 

Comments

Post a Comment

Popular posts from this blog

Observium: Configuring Microsoft Windows 2008 Server SNMP Agent

Image
1 . Open Control Pannel. 2. Right click on “ Programs & Features ” and click on “ Turn Windows Features on or off" 3 . Expand “ SNMP Services ” and select “ SNMP Service ” click Next 4. Click Install 5. Close  Programs & Features ” 6. Open Ädministraive Tools and clicket Services 7. Expand Configuration and select Services,  Select SNMP Service , right click and select properties 8 . Select the “ Agent ” tab 9. Type the contact name responsible for this server or email address 10 . Type the location eg, Hyderabad,India. 11 . Tick the following  Physical Datalink and subnetwork  SNMP Trap Service - Agent 12 . Select the “ Traps ” tab 13 . Type in the community string you are using in your environment and select “ Add to list ” 14 . Click “ Add ” 15 . Type the IP of the Observium ServerSNMP Trap Service - Traps 16 . Select “ Security ” tab 17 . Ensure “ Send ...
Read more

AWS: Introduction to AWS (Amazon Web Services)

Image
Amazon Web Services (AWS) Considering that you don't have an account with AWS ( Amazon Web Services) this Demo is divided into 5 steps 1. Creating/Sign Up for AWS account. 2. Creat ing an IAM user . 3. Creat ing a secure key pair. 4. Creat ing a Virtual Private Cloud (VPC) 5. Creat ing a security group for access. 1. Creating/Sign Up for AWS account When we sign up for the AWS account, we get automatic  access for all the services in the AWS, which includes the Amazon EC2 and we are charged for only the services which we chose and use. With Amazon EC2 we will pay for that what we use and if we are a  new AWS customer and we can get started with Amazon EC2 for free. If we have an AWS account already, we can skip the next task or else we can continue with the account creation. You can check the AWS Free Tier information here http://aws.amazon.com/free/ To create an AWS account, we needed to access the URL a). Connect to the URL ht...
Read more

System/Server Health Status using PowerShell

Image
System/Server Health Status using PowerShell Support team or System Administrators who are willing to get the system health check status can use this script. This script has been written in Powershell and it can generate   reports   in HTML format. This script can give you below information: Hardware: To Get Manufacturer, Model, No.   Of   Logical Processors, Installed Memory.   Code:  Get-WmiObject -class Win32_ComputerSystem -ComputerName $Computername | Select-Object Name,Domain,Manufacturer, Model,NumberOfLogicalProcessors,@{ Name = "Installed Memory (GB)" ; Expression = { "{0:N0}" -f( $_.TotalPhysicalMemory / 1gb ) } }            CPU Details: To Get  CPU speed, Current CPU Usage in %,  Code: Get-WmiObject win32_processor | Select-Object SystemName,@{ Expression = { $_.MaxClockSpeed...
Read more