Thought I would share this useful tip on how to script multiple SQL Server Agent jobs into one script in order to execute it on another server.

For example, you have ETLs and backups scheduled on a DEV server and you want to replicate the jobs onto the PROD server. If you have one or two jobs it would be easy to manually create them. When you have a quite a few more jobs that need to be replicated it can be quite tedious to manually create each one on the other server.

There is a simple trick for you to script out multiple jobs from SSMS and run them on another server.

Step 1:
Connect to the server where the jobs are located that you want to replicate. Expand the SQL Server Agent node in Object Explorer and click on the Jobs node and push F7 on your keyboard.

Alternatively, you can access the Object Explorer Details by clicking on View on the top Menu bar and selecting the second option Object Explorer Details

This will open up Object Explorer Details

Step 2:
Next you will select all the jobs that you would like to replicate. You can do this by holding Ctrl in on your keyboard and clicking on each job. Then right-click on one of the highlighted jobs and select Script job as -> Create To -> New Query Editor Window


Step 3:
This will script out all the jobs into one window. You will then need to do a find and replace to change the server name to the name of the server you will be executing this on.

You can do this by highlighting the server name. In this case BIDEV and pressing Ctrl + H on your keyboard which will open up the Find and Replace dialogue box. Next you will enter the name of the server you want to run the script on and click Replace All.

A pop up will appear to tell you how many occurrences were replaced.

Step 4:
The last step will be to copy and paste the script onto the server you would like to replicate the jobs on. Alternatively, you can save the script and copy the script over to the server you would like to run it on and open it up in SSMS, then click execute once open.


Leave a Reply

Your email address will not be published. Required fields are marked *