Geekly Articles each Day
📜 ⬆️ ⬇️

Transfer of tasks and schedules from one instance of MS SQL Server to another using T-SQL

Foreword


Quite often, it is necessary to transfer the Agent's tasks to another instance of MS SQL Server. Restoring the msdb database is not always the solution that is suitable, there are often cases when you need to transfer only the tasks of the Agent, as well as when switching to a newer version of MS SQL Server. So how can you transfer Agent jobs without restoring the msdb database?

In this article, an example of the implementation of the T-SQL script that copies the Agent's tasks from one instance of MS SQL Server to another will be analyzed. This solution was tested when transferring the tasks of the Agent from MS SQL Server 2012-2016 to MS SQL Server 2017.

Decision


We first describe the sequence of actions:

1) create a list of tasks that do not need to be transferred
2) transfer the tasks themselves
3) move the steps of the transferred tasks
4) to transfer the schedule of the transferred tasks
5) transfer a bunch of task schedules for transferred tasks
6) transfer target servers for migrated tasks
7) we register tasks and we activate their schedules, having transferred these tasks in an inactive mode (switching off of tasks)
8) assign an owner for all transferred tasks (for example, sa)
')
Now for each item we give an implementation in T-SQL.

All 8 steps must be performed in one block. But for better understanding, we will describe each block separately. Before performing these 8 steps, you also need to link an instance of MS SQL Server to which tasks will be copied.

So:

1) collect those tasks that do not need to be transferred:

Request
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 ( < GUID-  ,    > )

Thus, we got the table of non-portable tasks #tbl_notentity, which contains a pair of task schedule GUID and task GUID.

2) transfer the tasks themselves:

Request
 select *, 0 as IsAdd into #tbl_jobs from [-].[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 [-].[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;

First, we collect all the available tasks on the recipient server into the #tbl_jobs table. Then, using the MERGE instruction, we merge the [job_id] field into this table of all missing jobs from the source server, which are not in the #tbl_notentity table from step 1 of the algorithm. Inserted rows are marked as 1 in the IsAdd column. And further, we add all tasks to the [msdb]. [Dbo]. [Sysjobs] table of the recipient server from the #tbl_jobs table according to the condition IsAdd = 1. Thus, the transfer of those tasks to the recipient server, which are not in the #tbl_notentity table from step 1 of the algorithm, is performed.

3) move the tasks transferred tasks:

Request
 select *, 0 as IsAdd into #tbl_jobsteps from [-].[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 [-].[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;

First, we collect all the available job steps on the recipient server into the #tbl_jobsteps table. Then, using the MERGE instruction, we merge the [job_id] and [step_id] fields into this table of all missing job steps from the source server, which are not in the #tbl_notentity table from step 1 of the algorithm. Inserted rows are marked as 1 in the IsAdd column. And further, we add all job steps to the [msdb]. [Dbo]. [Sysjobsteps] table of the recipient server from the #tbl_jobsteps table according to the condition IsAdd = 1. Then we delete the table #tbl_jobsteps, t to further we do not need it anymore.

Thus, all the steps of those tasks are transferred to the recipient server, which are not in the #tbl_notentity table from step 1 of the algorithm.

4) to transfer the schedule of the transferred tasks:

Request
 select *, 0 as IsAdd into #tbl_sysschedules from [-].[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 [-].[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;

First, we collect all the available schedules on the receiving server into the #tbl_sysschedules table. Then, using the MERGE instruction, we merge the [schedule_uid] field into this table of all missing schedules from the source server, which are not in the #tbl_notentity table from step 1 of the algorithm. Inserted rows are marked as 1 in the IsAdd column. And further, we add all the schedules to the [msdb]. [Dbo]. [Sysschedules] table of the recipient server from the #tbl_sysschedules table by the condition IsAdd = 1. Then we delete the table #tbl_sysschedules, t to further, we no longer need it.

Thus, all schedules are transferred to the recipient server, which are not in the #tbl_notentity table from step 1 of the algorithm.

5) transfer a bunch of task schedules for transferred tasks:

Request
 select js.*, ss.[schedule_uid], 0 as IsAdd into #tbl_jobschedules from [-].[msdb].[dbo].[sysjobschedules] as js inner join [-].[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 [-].[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 [-].[msdb].[dbo].[sysjobschedules]([schedule_id] ,[job_id] ) select [schedule_id] ,[job_id] from #tbl_jobschedules where IsAdd=1; drop table #tbl_jobschedules;

First, we collect all the existing job schedule links on the recipient server into the #tbl_jobschedules table. Then, using the MERGE instruction, we merge the [job_id] and [schedule_uid] fields in this table of all missing connectives from the source server, which are not in the #tbl_notentity table from step 1 of the algorithm. Inserted rows are marked as 1 in the IsAdd column. And further, we add all schedules to the [msdb]. [Dbo]. [Sysjobschedules] table of the recipient server from the #tbl_jobschedules table according to the condition IsAdd = 1. Then we delete the table #tbl_jobschedules, t to further, we no longer need it.

Thus, the transfer of all bundles of schedules-tasks to the recipient server, which are not in the #tbl_notentity table of claim 1 of the algorithm, is performed.

6) transfer target servers for migrated tasks:

Request
 select *, 0 as IsAdd into #tbl_sysjobservers from [-].[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 [-].[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;

First, we collect all the existing job-target server connections on the recipient server into the #tbl_sysjobservers table. Then, using the MERGE instruction, we merge the [job_id] and [server_id] fields in this table of all missing links from the source server, which are not in the #tbl_notentity table from step 1 of the algorithm. Inserted rows are marked as 1 in the IsAdd column. And further, we add all links to the [msdb]. [Dbo]. [Sysjobservers] table of the recipient server from the #tbl_sysjobservers table according to the condition IsAdd = 1. Then we delete the tables #tbl_sysjobservers and #tbl_notentity, and we will no longer need them.

Thus, all the task-target server bundles are transferred to the recipient server, which are not in the #tbl_notentity table of claim 1 of the algorithm.

It is important to note that if there are target servers in the tasks that are different from the local one (that is, the identifier is not zero), then you must first transfer the definitions of these target servers themselves, and then produce step 6 of the algorithm.

7) we register tasks and we activate their schedules, having transferred these tasks in an inactive mode (switching off of tasks)

and

8) assign an owner for all transferred tasks (for example, sa)

Request
 declare @job_id uniqueidentifier; --    sa update sj set sj.[owner_sid]=0x01 from #tbl_jobs as t inner join [-].[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 [-].[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;

First, we assign the sa owner to all transferred tasks (we define the transferred tasks using the #tbl_jobs table). Then we register each transferred task and activate their schedules by calling the system stored procedure [msdb]. [Dbo] .sp_update_job on the recipient server to turn off the transferred tasks. And further, we delete the table #tbl_jobs, that is, it is not needed anymore.

Thus, all transferred tasks were assigned the owner of sa, and all these tasks were registered (and their schedules were activated) through their shutdown.
Further, the necessary tasks must be enabled by script or manually.

Here is the code for the entire script:

Request
 --  ,     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 ( < GUID-  ,    > ) --  select *, 0 as IsAdd into #tbl_jobs from [-].[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 [-].[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; --   select *, 0 as IsAdd into #tbl_jobsteps from [-].[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 [-].[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; --   select *, 0 as IsAdd into #tbl_sysschedules from [-].[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 [-].[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; --       select js.*, ss.[schedule_uid], 0 as IsAdd into #tbl_jobschedules from [-].[msdb].[dbo].[sysjobschedules] as js inner join [-].[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 [-].[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 [-].[msdb].[dbo].[sysjobschedules]([schedule_id] ,[job_id] ) select [schedule_id] ,[job_id] from #tbl_jobschedules where IsAdd=1; drop table #tbl_jobschedules; --   select *, 0 as IsAdd into #tbl_sysjobservers from [-].[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 [-].[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; --     ,       ( ) declare @job_id uniqueidentifier; --    sa update sj set sj.[owner_sid]=0x01 from #tbl_jobs as t inner join [-].[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 [-].[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;

Result


This article described an example of implementing a T-SQL script that allows you to transfer tasks and schedules of the Agent from one instance of MS SQL Server to another. Also, this approach can be implemented using other means. For example, PowerShell or C #.

Sources:


" Msdb
» SQL Server Agent Tables
» Sp_update_job

Source: https://habr.com/ru/post/341196/

More articles:


All Articles