Setting up SQL Server to email you when a SQL job fails

In this post I will list all the steps needed to be completed in order to have SQL Server send email notifications when a job fails.

Step 1.
Enable Database Mail unless it has already been done. Make sure you have a profile set up. I will not go into details on this step as it's pretty straight-forward. Make sure your test email will go through though. In our case our STMP server only accepts email messages from specific servers so if I set up database mail on a new SQL Server then I will have to notify our server admin to add that server to the white list.

Step 2.
Create an Operator. This item is found under SQL Server Agent in SSMS.
This step is pretty straight-forward as well. You need to do is name the operator and specify an email address for it. At my current job we do not have a large group of database admins so I only have one operator with a distribution group email address that goes to exactly 2 people.

Step 3.
Enable mail profile for SQL Server Agent. This can be done in the Properties of SQL Server Agent, Alert System section. Check the Enable mail profile item, select Database Mail as the Mail system and select the Mail profile that was created in Step 1. In this section you may enable the a fail-safe operator as well by selecting the Operator you created in Step 2.

Step 4.
Restart SQL Server Agent! This bites me all the time. You do everything but this step and you wonder why it does not work. Because you forgot to restart the Agent!

Step 5.
Edit the properties of the SQL Job for which you want to set up email notifications. In the Notifications section check E-mail and specify the Operator you set up in Step 2. You obviously want the notification to be sent out when the job fails. However, in some unique scenarios you also want notifications when the succeeds or simply just completes. I usually also check the Write to the Windows Application event log as well. Emails can get deleted. You need to make of an effort to get rid of an event log. Save the SQL Job and you are done!

Migrating SQL Jobs

About once a year I am faced with the fact to migrate one of our SQL Servers to a better, bigger, newer server. Usually it's not a big deal because for the most part it can be done by backing up and restoring the database. How about migrating the SQL Jobs though? Sure, that can be done as well by backing up and restoring the MSDN database but if you are also upgrading SQL Server that may not be a wise idea.

There are a few ways this can be done. SSIS has a Transfer Jobs Task but I ran into a problem with that as most of the jobs had the server specified like this: @server=N'SERVERNAME' so the task was not able to transfer those jobs. Individually scripting out SQL Jobs is possible of course if you only have a handful. In my case I had 87 SQL Jobs and frankly, I am too lazy for that much work. So I found a shortcut.

Click Jobs in Management Studio and then click F7. That will pull up Object Browser with the list of all the SQL Jobs. Highlight all and then right click and select Script Job As>Create To>New Query Window. That will script out all your jobs. Once you have them scripted out you can just a search and replace for server names or other variables you need to change. Then you change the connection to your new SQL Server, hit Execute and you are done!

In my case I was almost done. I also had some Maintenance Jobs to transfer as well. Those are basically SSIS packages kept on SQL Server as opposed to in the File System. The solution is to export them to the file system, change the connection and then import them into the new SQL Server. Here are the steps to accomplish that.

1. Connect to your server's Integration Services in SSMS. You can do that by hit Connect>Integration Services. Once you are connected you will find the maintenance plan packages under Stored Packages>MSDB>Maintenance Plans.
2. Export all packages. You can do that by right clicking each package then selecting Export Package. On the dialog box that box up you need to select File System in the Package location section. Then navigate to the location where you want to save your package. I left the default in the Protection level section.
   
   
3. Open the packages in notepad and change the connection information. Just search for the name of the server and replace it with the name of the new server. This step is necessary because you are unable to edit the Local Server Connection in the package editor. You may also change other things as well such as backup locations and such. Save the file.
4. Import the package. Connect to your new servers Integration Services. Navigate down to the location where the Maintenance Plans are located which should be the same as in the source server (see step 1.) Right click Maintenance Plans and select Import package. Select File System in the Package location then in the Package path using the ellipsis button navigate to the location where you exported your packages to. The Package name will be automatically filled in although you could change it.
5.  If by accident you skipped step 3, don't fret, you can still do it. You will have to repeat step 4 again and this time you will be overwriting your existing package.
6. You probably want to double-check on each package to make sure there are no other adjustments need to be done. Connect to the new SQL Server's database engine this time and right-click Modify on the maintenance plans. (Management>Maintenance Plans)

That's all there is to it. I hope I saved someone time with this post.