Thursday, April 29, 2010

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.

No comments:

Post a Comment