Transferring Jobs and Schedules between Instances using T-SQL

Introduction

Quite often, there is a need to transfer Agent jobs to a different instance of MS SQL Server. Restoring a msdb database will not always be the best decision – there are cases in which you will need to transfer only the Agent jobs specifically, and these cases are not uncommon. Also, this wouldn’t work if the recipient MS SQL Server instance is of a newer version than the one you’re transferring the jobs from. So, how can you transfer the Agent jobs without restoring the msdb database?

We will look at an example of a T-SQL script which copies the Agent jobs from one instance of MS SQL Server to another. This solution was tested by transferring jobs from MS SQL Server 2012-2016 to MS SQL Server 2017.

The solution

First of all, let’s lay out the steps we will need to take:

1) Create a list of jobs which don’t need to be transferred
2) Transfer the required jobs
3) Transfer the steps of the transferred jobs
4) Transfer the schedules of the transferred jobs
5) Transfer the job/schedule pairs of the transferred jobs
6) Transfer the target servers of the transferred jobs
7) Register the jobs and activate their schedules, consequently inactivating the jobs
8) Assign an owner for all of the transferred jobs (sa, for example).

Now, let’s realize each of the steps in T-SQL.

All of these 8 steps should be executed in one block. However, to make things more comprehensive, we’ll describe each of the steps separately. Please keep in mind that you will need to link the recipient MS SQL Server instance before executing these steps.

So, here’s what we need to do:

1) Create a list of jobs which don’t need to be transferred:

Executing this gives us a table of the ‘non-transfer’ jobs – #tbl_notentity. It will contain specific job GUIDs paired with the respective GUIDs of their schedules.

2) Transfer the jobs:

Code

We’ll need to gather all jobs currently held on the recipient server into the #tbl_jobs table. With thr MERGE instruction, we’ll merge all jobs not included in the #tbl_notentity table we created in step 1 into the #tbl_jobs table using the [job_id] field. When this is done, we’ll mark all the rows we just inserted with a ‘1’ in the ‘IsAdd‘ column. Next, let’s add all the jobs from the #tbl_jobs table to the [msdb].[dbo].[sysjobs] of the recipient server using ‘IsAdd=1’ as a condition.

In this way, we have transferred the jobs absent from the #tbl_notentity table to the recipient server.

3) Transfer the steps of the transferred jobs:

Code

Let’s collect all job steps we have on the recipient server into the #tbl_jobsteps table. With the MERGE instruction, we’ll merge all job steps absent from the #tbl_notentity table we created in step 1 into the #tbl_jobsteps table using the [job_id] and [step_id] fields. The rows we just inserted will need to be marked with a ‘1’ in the ‘IsAdd’ column. Next, let’s add all the job steps from the #tbl_jobsteps table to the [msdb].[dbo].[sysjobsteps] of the recipient server using ‘IsAdd=1’ as a condition.

Now, let’s delete the #tbl_jobsteps table – we don’t need it any more.

In this way, we have transferred the steps of the jobs not included in the #tbl_notentity table to the recipient server.

4) Transfer the schedules of the transferred jobs:

Code

Firstly, we’ll collect all schedules from the recipient server into a #tbl_sysschedules table. With the MERGE instruction, we’ll merge all job steps absent from the #tbl_notentity table we created in step 1 into the #tbl_sysschedules table using the [schedule_uid] field. We’ll mark the rows that were inserted this way as ‘1’ in the ‘IsAdd column’. Next, we’ll add all schedules from the #tbl_sysschedules table into the [msdb].[dbo].[sysschedules] table of the target server using the ‘IsAdd=1’ condition.

We’ll delete the #tbl_sysschedules table as we won’t need it in the following steps.

We have successfully transferred the schedules of the jobs not included in the #tbl_notentity table to the recipient server.

5) Transfer the job/schedule pairs of the transferred jobs:

We’ll need to collect all job/schedule pairs located on the recipient server into the #tbl_jobschedules table. Next, using the MERGE function, we’ll merge all job/schedule pairs absent from the #tbl_notentity table we created in step 1 into the #tbl_jobschedules table – the conditions we need to utilize here are [job_id] and [schedule_uid]. The rows we just inserted will need to be marked with a ‘1’ in the ‘IsAdd column’.

Now, let’s add all the schedules from the #tbl_jobschedules table to the [msdb].[dbo].[sysjobschedules] table of the recipient server using the ‘IsAdd=1’ condition. The #tbl_jobschedules table can be deleted as we won’t use it any more.

We have successfully transferred the job/schedule pairs not included in the #tbl_notentity table to the recipient server.

6) Transfer the target servers of the transferred jobs:

 

We’ll need to collect all job/target server pairs located on the recipient server into the #tbl_sysjobservers table. With the MERGE instruction, we’ll merge all pairs absent from the #tbl_notentity table we created in step 1 into the #tbl_sysjobservers table using the [job_id] and [server_id] fields. When this is done, we’ll mark all the rows we just inserted with a ‘1’ in the ‘IsAdd’ column. Next, we’ll add all schedules from the #tbl_sysjobservers table into the [msdb].[dbo].[sysjobservers] table of the target server using the ‘IsAdd=1’ condition.

Now, we can delete the #tbl_sysjobservers and #tbl_notentity tables.

In this way, we have transferred the job/target server pairs absent from the #tbl_notentity table to the recipient server.

Please keep in mind that if the jobs have target servers different from the local one, i.e., the identifier is not equal to 0, you will first need to transfer the definitions of those servers before proceeding to step 6.

7) Register the jobs and activate their schedules, consequently inactivating the jobs

and

8) Assign an owner to all of the transferred jobs (‘sa’, for example):

 

Firstly, we’ll assign an owner (‘sa’, in this case) to each of the transferred jobs. The transferred jobs can be identified using the #tbl_jobs table. Then, we’ll register each of the transferred jobs and activate their schedules – this can be done by calling the [msdb.][dbo].sp_update_job system procedure on the recipient server. This will also inactivate the transferred jobs. You can deletr the #tbl_jobs table as we won’t use it any more.

In this way, we have assigned an owner (‘sa’) to each of the transferred jobs. All of these jobs have been registered and deactivated, while the respective schedules have been activated.
Now, all the required jobs can be turned on manually or by a script.

Here’s the full code of the script we have written:

Code

The result

We have seen an example of a T-SQL script which transfers the Agent jobs and schedules from one instance of a MS SQL Server to another. This approach can also be implemented through different means, e.g., PowerShell or C#.

Sources:

msdb
SQL Server Agent Tables
sp_update_job

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov