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:
select ss.[schedule_uid] ,js.[job_id] into #tbl_notentity from [msdb].[dbo].[sysjobschedules] as js inner join [msdb].[dbo].[sysschedules] as ss on js.[schedule_id]=ss.[schedule_id] where [job_id] in ( <list of the 'non-transfer' job GUIDs> )
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:
[expand title =”Code“]
select *, 0 as IsAdd
into #tbl_jobs
from [RECIPIENT-SERVER].[msdb].[dbo].[sysjobs];
;with src as (
select *
from [msdb].[dbo].[sysjobs] as src
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[job_id]=src.[job_id]
)
)
merge #tbl_jobs as trg
using src on trg.[job_id]=src.[job_id]
when not matched by target then
INSERT ([job_id]
,[originating_server_id]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category_id]
,[owner_sid]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator_id]
,[notify_netsend_operator_id]
,[notify_page_operator_id]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
,[IsAdd])
VALUES (src.[job_id]
,src.[originating_server_id]
,src.[name]
,src.[enabled]
,src.[description]
,src.[start_step_id]
,src.[category_id]
,src.[owner_sid]
,src.[notify_level_eventlog]
,src.[notify_level_email]
,src.[notify_level_netsend]
,src.[notify_level_page]
,src.[notify_email_operator_id]
,src.[notify_netsend_operator_id]
,src.[notify_page_operator_id]
,src.[delete_level]
,src.[date_created]
,src.[date_modified]
,src.[version_number]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysjobs]([job_id]
,[originating_server_id]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category_id]
,[owner_sid]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator_id]
,[notify_netsend_operator_id]
,[notify_page_operator_id]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number])
select [job_id]
,[originating_server_id]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category_id]
,[owner_sid]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator_id]
,[notify_netsend_operator_id]
,[notify_page_operator_id]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
from #tbl_jobs
where IsAdd=1;
[/expand]
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:
[expand title =”Code“]
select *, 0 as IsAdd
into #tbl_jobsteps
from [RECIPIENT-SERVER].[msdb].[dbo].[sysjobsteps];
;with src as (
select *
from [msdb].[dbo].[sysjobsteps] as src
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[job_id]=src.[job_id]
)
)
merge #tbl_jobsteps as trg
using src on trg.[job_id]=src.[job_id] and trg.[step_id]=src.[step_id]
when not matched by target then
INSERT ([job_id]
,[step_id]
,[step_name]
,[subsystem]
,[command]
,[flags]
,[additional_parameters]
,[cmdexec_success_code]
,[on_success_action]
,[on_success_step_id]
,[on_fail_action]
,[on_fail_step_id]
,[server]
,[database_name]
,[database_user_name]
,[retry_attempts]
,[retry_interval]
,[os_run_priority]
,[output_file_name]
,[last_run_outcome]
,[last_run_duration]
,[last_run_retries]
,[last_run_date]
,[last_run_time]
,[proxy_id]
,[step_uid]
,[IsAdd])
VALUES (src.[job_id]
,src.[step_id]
,src.[step_name]
,src.[subsystem]
,src.[command]
,src.[flags]
,src.[additional_parameters]
,src.[cmdexec_success_code]
,src.[on_success_action]
,src.[on_success_step_id]
,src.[on_fail_action]
,src.[on_fail_step_id]
,src.[server]
,src.[database_name]
,src.[database_user_name]
,src.[retry_attempts]
,src.[retry_interval]
,src.[os_run_priority]
,src.[output_file_name]
,src.[last_run_outcome]
,src.[last_run_duration]
,src.[last_run_retries]
,src.[last_run_date]
,src.[last_run_time]
,src.[proxy_id]
,src.[step_uid]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysjobsteps]([job_id]
,[step_id]
,[step_name]
,[subsystem]
,[command]
,[flags]
,[additional_parameters]
,[cmdexec_success_code]
,[on_success_action]
,[on_success_step_id]
,[on_fail_action]
,[on_fail_step_id]
,[server]
,[database_name]
,[database_user_name]
,[retry_attempts]
,[retry_interval]
,[os_run_priority]
,[output_file_name]
,[last_run_outcome]
,[last_run_duration]
,[last_run_retries]
,[last_run_date]
,[last_run_time]
,[proxy_id]
,[step_uid])
select [job_id]
,[step_id]
,[step_name]
,[subsystem]
,[command]
,[flags]
,[additional_parameters]
,[cmdexec_success_code]
,[on_success_action]
,[on_success_step_id]
,[on_fail_action]
,[on_fail_step_id]
,[server]
,[database_name]
,[database_user_name]
,[retry_attempts]
,[retry_interval]
,[os_run_priority]
,[output_file_name]
,[last_run_outcome]
,[last_run_duration]
,[last_run_retries]
,[last_run_date]
,[last_run_time]
,[proxy_id]
,[step_uid]
from #tbl_jobsteps
where IsAdd=1;
drop table #tbl_jobsteps;
[/expand]
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:
[expand title =”Code“]
select *, 0 as IsAdd
into #tbl_sysschedules
from [RECIPIENT-SERVER].[msdb].[dbo].[sysschedules];
;with src as (
select *
from [msdb].[dbo].[sysschedules] as src
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[schedule_uid]=src.[schedule_uid]
)
)
merge #tbl_sysschedules as trg
using src on trg.[schedule_uid]=src.[schedule_uid]
when not matched by target then
INSERT ([schedule_id]
,[schedule_uid]
,[originating_server_id]
,[name]
,[owner_sid]
,[enabled]
,[freq_type]
,[freq_interval]
,[freq_subday_type]
,[freq_subday_interval]
,[freq_relative_interval]
,[freq_recurrence_factor]
,[active_start_date]
,[active_end_date]
,[active_start_time]
,[active_end_time]
,[date_created]
,[date_modified]
,[version_number]
,[IsAdd])
VALUES (src.[schedule_id]
,src.[schedule_uid]
,src.[originating_server_id]
,src.[name]
,src.[owner_sid]
,src.[enabled]
,src.[freq_type]
,src.[freq_interval]
,src.[freq_subday_type]
,src.[freq_subday_interval]
,src.[freq_relative_interval]
,src.[freq_recurrence_factor]
,src.[active_start_date]
,src.[active_end_date]
,src.[active_start_time]
,src.[active_end_time]
,src.[date_created]
,src.[date_modified]
,src.[version_number]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysschedules]([schedule_uid]
,[originating_server_id]
,[name]
,[owner_sid]
,[enabled]
,[freq_type]
,[freq_interval]
,[freq_subday_type]
,[freq_subday_interval]
,[freq_relative_interval]
,[freq_recurrence_factor]
,[active_start_date]
,[active_end_date]
,[active_start_time]
,[active_end_time]
,[date_created]
,[date_modified]
,[version_number])
select [schedule_uid]
,[originating_server_id]
,[name]
,[owner_sid]
,[enabled]
,[freq_type]
,[freq_interval]
,[freq_subday_type]
,[freq_subday_interval]
,[freq_relative_interval]
,[freq_recurrence_factor]
,[active_start_date]
,[active_end_date]
,[active_start_time]
,[active_end_time]
,[date_created]
,[date_modified]
,[version_number]
from #tbl_sysschedules
where IsAdd=1;
drop table #tbl_sysschedules;
[/expand]
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:
select js.*, ss.[schedule_uid], 0 as IsAdd
into #tbl_jobschedules
from [RECIPIENT-SERVER].[msdb].[dbo].[sysjobschedules] as js
inner join [RECIPIENT-SERVER].[msdb].[dbo].[sysschedules] as ss on js.[schedule_id]=ss.[schedule_id];
;with src as (
select js.[job_id]
,js.[next_run_date]
,js.[next_run_time]
,ss.[schedule_uid]
,serv.[schedule_id]
from [msdb].[dbo].[sysjobschedules] as js
inner join [msdb].[dbo].[sysschedules] as ss on js.[schedule_id]=ss.[schedule_id]
inner join [RECIPIENT SERVER].[msdb].[dbo].[sysschedules] as serv on serv.[schedule_uid]=ss.[schedule_uid]
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[schedule_uid]=ss.[schedule_uid]
)
)
merge #tbl_jobschedules as trg
using src on trg.[job_id]=src.[job_id] and trg.[schedule_uid]=src.[schedule_uid]
when not matched by target then
INSERT ([schedule_id]
,[schedule_uid]
,[job_id]
,[next_run_date]
,[next_run_time]
,[IsAdd])
VALUES (src.[schedule_id]
,src.[schedule_uid]
,src.[job_id]
,src.[next_run_date]
,src.[next_run_time]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysjobschedules]([schedule_id]
,[job_id]
)
select [schedule_id]
,[job_id]
from #tbl_jobschedules
where IsAdd=1;
drop table #tbl_jobschedules;
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:
select *, 0 as IsAdd
into #tbl_sysjobservers
from [RECIPIENT-SERVER].[msdb].[dbo].[sysjobservers];
;with src as (
select *
from [msdb].[dbo].[sysjobservers] as src
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[job_id]=src.[job_id]
)
)
merge #tbl_sysjobservers as trg
using src on trg.[job_id]=src.[job_id] and trg.[server_id]=src.[server_id]
when not matched by target then
INSERT ([job_id]
,[server_id]
,[last_run_outcome]
,[last_outcome_message]
,[last_run_date]
,[last_run_time]
,[last_run_duration]
,[IsAdd])
VALUES (src.[job_id]
,src.[server_id]
,src.[last_run_outcome]
,src.[last_outcome_message]
,src.[last_run_date]
,src.[last_run_time]
,src.[last_run_duration]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysjobservers]([job_id]
,[server_id]
,[last_run_outcome]
,[last_outcome_message]
,[last_run_date]
,[last_run_time]
,[last_run_duration])
select [job_id]
,[server_id]
,[last_run_outcome]
,[last_outcome_message]
,[last_run_date]
,[last_run_time]
,[last_run_duration]
from #tbl_sysjobservers
where IsAdd=1;
drop table #tbl_sysjobservers;
drop table #tbl_notentity;
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):
declare @job_id uniqueidentifier; --assigning sa as an owner for the new jobs update sj set sj.[owner_sid]=0x01 from #tbl_jobs as t inner join [RECIPIENT-SERVER].[msdb].[dbo].[sysjobs] as sj on t.[job_id]=sj.[job_id] where [IsAdd]=1; while(exists(select top(1) 1 from #tbl_jobs where [IsAdd]=1)) begin select top(1) @job_id=[job_id] from #tbl_jobs where [IsAdd]=1; EXEC [RECIPIENT-SERVER].[msdb].[dbo].sp_update_job @job_id=@job_id, @enabled=0 delete from #tbl_jobs where [job_id]=@job_id; end drop table #tbl_jobs;
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:
[expand title =”Code“]
--collecting the jobs that don't need to be transferred
select ss.[schedule_uid]
,js.[job_id]
into #tbl_notentity
from [msdb].[dbo].[sysjobschedules] as js
inner join [msdb].[dbo].[sysschedules] as ss on js.[schedule_id]=ss.[schedule_id]
where [job_id] in (
<list of the 'non-transfer' job GUIDs>
)
--transferring jobs
select *, 0 as IsAdd
into #tbl_jobs
from [RECIPIENT-SERVER].[msdb].[dbo].[sysjobs];
;with src as (
select *
from [msdb].[dbo].[sysjobs] as src
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[job_id]=src.[job_id]
)
)
merge #tbl_jobs as trg
using src on trg.[job_id]=src.[job_id]
when not matched by target then
INSERT ([job_id]
,[originating_server_id]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category_id]
,[owner_sid]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator_id]
,[notify_netsend_operator_id]
,[notify_page_operator_id]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
,[IsAdd])
VALUES (src.[job_id]
,src.[originating_server_id]
,src.[name]
,src.[enabled]
,src.[description]
,src.[start_step_id]
,src.[category_id]
,src.[owner_sid]
,src.[notify_level_eventlog]
,src.[notify_level_email]
,src.[notify_level_netsend]
,src.[notify_level_page]
,src.[notify_email_operator_id]
,src.[notify_netsend_operator_id]
,src.[notify_page_operator_id]
,src.[delete_level]
,src.[date_created]
,src.[date_modified]
,src.[version_number]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysjobs]([job_id]
,[originating_server_id]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category_id]
,[owner_sid]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator_id]
,[notify_netsend_operator_id]
,[notify_page_operator_id]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number])
select [job_id]
,[originating_server_id]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category_id]
,[owner_sid]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator_id]
,[notify_netsend_operator_id]
,[notify_page_operator_id]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
from #tbl_jobs
where IsAdd=1;
--drop table #tbl_jobs;
--transferring job steps
select *, 0 as IsAdd
into #tbl_jobsteps
from [RECIPIENT-SERVER].[msdb].[dbo].[sysjobsteps];
;with src as (
select *
from [msdb].[dbo].[sysjobsteps] as src
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[job_id]=src.[job_id]
)
)
merge #tbl_jobsteps as trg
using src on trg.[job_id]=src.[job_id] and trg.[step_id]=src.[step_id]
when not matched by target then
INSERT ([job_id]
,[step_id]
,[step_name]
,[subsystem]
,[command]
,[flags]
,[additional_parameters]
,[cmdexec_success_code]
,[on_success_action]
,[on_success_step_id]
,[on_fail_action]
,[on_fail_step_id]
,[server]
,[database_name]
,[database_user_name]
,[retry_attempts]
,[retry_interval]
,[os_run_priority]
,[output_file_name]
,[last_run_outcome]
,[last_run_duration]
,[last_run_retries]
,[last_run_date]
,[last_run_time]
,[proxy_id]
,[step_uid]
,[IsAdd])
VALUES (src.[job_id]
,src.[step_id]
,src.[step_name]
,src.[subsystem]
,src.[command]
,src.[flags]
,src.[additional_parameters]
,src.[cmdexec_success_code]
,src.[on_success_action]
,src.[on_success_step_id]
,src.[on_fail_action]
,src.[on_fail_step_id]
,src.[server]
,src.[database_name]
,src.[database_user_name]
,src.[retry_attempts]
,src.[retry_interval]
,src.[os_run_priority]
,src.[output_file_name]
,src.[last_run_outcome]
,src.[last_run_duration]
,src.[last_run_retries]
,src.[last_run_date]
,src.[last_run_time]
,src.[proxy_id]
,src.[step_uid]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysjobsteps]([job_id]
,[step_id]
,[step_name]
,[subsystem]
,[command]
,[flags]
,[additional_parameters]
,[cmdexec_success_code]
,[on_success_action]
,[on_success_step_id]
,[on_fail_action]
,[on_fail_step_id]
,[server]
,[database_name]
,[database_user_name]
,[retry_attempts]
,[retry_interval]
,[os_run_priority]
,[output_file_name]
,[last_run_outcome]
,[last_run_duration]
,[last_run_retries]
,[last_run_date]
,[last_run_time]
,[proxy_id]
,[step_uid])
select [job_id]
,[step_id]
,[step_name]
,[subsystem]
,[command]
,[flags]
,[additional_parameters]
,[cmdexec_success_code]
,[on_success_action]
,[on_success_step_id]
,[on_fail_action]
,[on_fail_step_id]
,[server]
,[database_name]
,[database_user_name]
,[retry_attempts]
,[retry_interval]
,[os_run_priority]
,[output_file_name]
,[last_run_outcome]
,[last_run_duration]
,[last_run_retries]
,[last_run_date]
,[last_run_time]
,[proxy_id]
,[step_uid]
from #tbl_jobsteps
where IsAdd=1;
drop table #tbl_jobsteps;
--transferring job schedules
select *, 0 as IsAdd
into #tbl_sysschedules
from [RECIPIENT-SERVER].[msdb].[dbo].[sysschedules];
;with src as (
select *
from [msdb].[dbo].[sysschedules] as src
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[schedule_uid]=src.[schedule_uid]
)
)
merge #tbl_sysschedules as trg
using src on trg.[schedule_uid]=src.[schedule_uid]
when not matched by target then
INSERT ([schedule_id]
,[schedule_uid]
,[originating_server_id]
,[name]
,[owner_sid]
,[enabled]
,[freq_type]
,[freq_interval]
,[freq_subday_type]
,[freq_subday_interval]
,[freq_relative_interval]
,[freq_recurrence_factor]
,[active_start_date]
,[active_end_date]
,[active_start_time]
,[active_end_time]
,[date_created]
,[date_modified]
,[version_number]
,[IsAdd])
VALUES (src.[schedule_id]
,src.[schedule_uid]
,src.[originating_server_id]
,src.[name]
,src.[owner_sid]
,src.[enabled]
,src.[freq_type]
,src.[freq_interval]
,src.[freq_subday_type]
,src.[freq_subday_interval]
,src.[freq_relative_interval]
,src.[freq_recurrence_factor]
,src.[active_start_date]
,src.[active_end_date]
,src.[active_start_time]
,src.[active_end_time]
,src.[date_created]
,src.[date_modified]
,src.[version_number]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysschedules]([schedule_uid]
,[originating_server_id]
,[name]
,[owner_sid]
,[enabled]
,[freq_type]
,[freq_interval]
,[freq_subday_type]
,[freq_subday_interval]
,[freq_relative_interval]
,[freq_recurrence_factor]
,[active_start_date]
,[active_end_date]
,[active_start_time]
,[active_end_time]
,[date_created]
,[date_modified]
,[version_number])
select [schedule_uid]
,[originating_server_id]
,[name]
,[owner_sid]
,[enabled]
,[freq_type]
,[freq_interval]
,[freq_subday_type]
,[freq_subday_interval]
,[freq_relative_interval]
,[freq_recurrence_factor]
,[active_start_date]
,[active_end_date]
,[active_start_time]
,[active_end_time]
,[date_created]
,[date_modified]
,[version_number]
from #tbl_sysschedules
where IsAdd=1;
drop table #tbl_sysschedules;
--transferring job/schedule pairs
select js.*, ss.[schedule_uid], 0 as IsAdd
into #tbl_jobschedules
from [RECIPIENT-SERVER].[msdb].[dbo].[sysjobschedules] as js
inner join [RECIPIENT-SERVER].[msdb].[dbo].[sysschedules] as ss on js.[schedule_id]=ss.[schedule_id];
;with src as (
select js.[job_id]
,js.[next_run_date]
,js.[next_run_time]
,ss.[schedule_uid]
,serv.[schedule_id]
from [msdb].[dbo].[sysjobschedules] as js
inner join [msdb].[dbo].[sysschedules] as ss on js.[schedule_id]=ss.[schedule_id]
inner join [RECIPIENT-SERVER].[msdb].[dbo].[sysschedules] as serv on serv.[schedule_uid]=ss.[schedule_uid]
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[schedule_uid]=ss.[schedule_uid]
)
)
merge #tbl_jobschedules as trg
using src on trg.[job_id]=src.[job_id] and trg.[schedule_uid]=src.[schedule_uid]
when not matched by target then
INSERT ([schedule_id]
,[schedule_uid]
,[job_id]
,[next_run_date]
,[next_run_time]
,[IsAdd])
VALUES (src.[schedule_id]
,src.[schedule_uid]
,src.[job_id]
,src.[next_run_date]
,src.[next_run_time]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysjobschedules]([schedule_id]
,[job_id]
)
select [schedule_id]
,[job_id]
from #tbl_jobschedules
where IsAdd=1;
drop table #tbl_jobschedules;
--transferring target servers
select *, 0 as IsAdd
into #tbl_sysjobservers
from [RECIPIENT-SERVER].[msdb].[dbo].[sysjobservers];
;with src as (
select *
from [msdb].[dbo].[sysjobservers] as src
where not exists (
select top(1) 1 from #tbl_notentity as t where t.[job_id]=src.[job_id]
)
)
merge #tbl_sysjobservers as trg
using src on trg.[job_id]=src.[job_id] and trg.[server_id]=src.[server_id]
when not matched by target then
INSERT ([job_id]
,[server_id]
,[last_run_outcome]
,[last_outcome_message]
,[last_run_date]
,[last_run_time]
,[last_run_duration]
,[IsAdd])
VALUES (src.[job_id]
,src.[server_id]
,src.[last_run_outcome]
,src.[last_outcome_message]
,src.[last_run_date]
,src.[last_run_time]
,src.[last_run_duration]
,1);
insert into [RECIPIENT-SERVER].[msdb].[dbo].[sysjobservers]([job_id]
,[server_id]
,[last_run_outcome]
,[last_outcome_message]
,[last_run_date]
,[last_run_time]
,[last_run_duration])
select [job_id]
,[server_id]
,[last_run_outcome]
,[last_outcome_message]
,[last_run_date]
,[last_run_time]
,[last_run_duration]
from #tbl_sysjobservers
where IsAdd=1;
drop table #tbl_sysjobservers;
drop table #tbl_notentity;
--registering jobs and activating their schedules, consequently deactivating the jobs
declare @job_id uniqueidentifier;
--make the owner of new tasks sa
update sj
set sj.[owner_sid]=0x01
from #tbl_jobs as t
inner join [RECIPIENT-SERVER].[msdb].[dbo].[sysjobs] as sj on t.[job_id]=sj.[job_id]
where [IsAdd]=1;
while(exists(select top(1) 1 from #tbl_jobs where [IsAdd]=1))
begin
select top(1)
@job_id=[job_id]
from #tbl_jobs
where [IsAdd]=1;
EXEC [RECIPIENT-SERVER].[msdb].[dbo].sp_update_job @job_id=@job_id,
@enabled=0
delete from #tbl_jobs
where [job_id]=@job_id;
end
drop table #tbl_jobs;
[/expand]
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
