Friday, 2 June 2017

Copy SQL Jobs from one server to another

Copy SQL Jobs from one server to another

There are situations in a DBA's job when he is asked to do a copy of all the SQL Agent Jobs from one server to another. If it is only about a couple of jobs then one can easily script the jobs on the source server and execute that script on the destination server. And the task is done.

But what if you have 100+ SQL Agent jobs that need to be copied as it is. And what if this copy activity has to be repeated every week or say twice in a week. Does scripting out the job on the source server and executing the same on destination server will be feasible in such a case? No right. We'll discuss about an excellent solution for this today.

We will create an SSIS package for this requirement,

1) Open BIDS

2) Select Transfer Jobs task and drag it to the design pane.

Copy SQL Jobs from one server to another

3) Double Click the "Transfer Job Task" to open the configuration window. Set the name and description as per your requirement.

Copy SQL Jobs from one server to another

4) Click on Jobs and enter source and destination server details. You should check to test your connections as well to confirm if they are working fine.

Copy SQL Jobs from one server to another

5) Once connections are configured, move on to the next item which is about jobs. It asks you that if you need all the jobs to be transferred. Select NO in case you have only a selected jobs to be transferred to destination server. Select yes otherwise. If you have selected No then you have the option to select the jobs which you wish to transfer in the next item.

6) Once you are done with selecting the jobs that need to be copied to another server, move on the next item which talks about options like what do you want to do if the job already exists on the server. You get option of overwriting the old job or fail the task. Second option is that you need to enable the jobs on the destination server. There are cases when people just want the copy of their important jobs on their DR Site and don't want to run them. You can achieve this by selecting False in EnableJobatDestination.

Once you have saved your package, you can schedule it via a SQL Server Job or Windows task scheduler. So you have seen that with the help of SSIS package it becomes easier to keep jobs in sync between your PROD and DR Servers.You can just set a notification for your SQL job or windows task which you have created to copy sql jobs to monitor the activity.


Post a comment