Pull to refresh

Перенос заданий и расписаний с одного экземпляра MS SQL Server на другой средствами T-SQL

Reading time 16 min
Views 16K

Предисловие


Довольно часто бывает необходимо перенести задания Агента на другой экземпляр MS SQL Server. Восстановление базы данных msdb невсегда именно то решение, которое подойдет, т к нередки случаи, когда нужно перенести именно только задания Агента, а также при переходе на более новую версию MS SQL Server. Так как же можно перенести задания Агента без восстановления базы данных msdb?

В данной статье будет разобран пример реализации скрипта T-SQL, который копирует задания Агента с одного экземпляра MS SQL Server на другой. Данное решение было опробовано при переносе заданий Агента с MS SQL Server 2012-2016 на MS SQL Server 2017.

Решение


Опишем сначала саму последовательность действий:

1) создать список заданий, который переносить не нужно
2) перенести сами задания
3) перенести шаги перенесенных заданий
4) перенести расписания перенесенных заданий
5) перенести связку расписания-задания для перенесенных заданий
6) перенести целевые сервера для перенесенных заданий
7) регистрируем задания и активизируем их расписания, переведя в неактивный режим эти задания (выключением заданий)
8) назначаем владельца для всех перенесенных заданий (например, sa)

Теперь для каждого пункта приведем реализацию на T-SQL.

Все 8 шагов должны выполняться одним блоком. Но для лучшего понимания, опишем каждый блок отдельно. Перед выполнением этих 8-ми шагов также необходимо связать экземпляр MS SQL Server, на который будут скопированы задания.

Итак:

1) собираем те задания, которые переносить не нужно:

Запрос
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-ов тех заданий, которые переносить не нужно>
)

Таким образом, получили таблицу непереносимых заданий #tbl_notentity, в которой содержится пара GUID расписания задания и GUID самого задания.

2) перенести сами задания:

Запрос
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;

Сначала собираем все имеющиеся задания на сервере-получателе в таблицу #tbl_jobs. Затем с помощью инструкции MERGE производим слияние по полю [job_id] в эту таблицу всех недостающих заданий с сервера-источника, которых нет в таблице #tbl_notentity из п.1 алгоритма. Вставленные строки помечаем как 1 в столбце IsAdd. И далее, добавляем все задания в таблицу [msdb].[dbo].[sysjobs] сервера-получателя из таблицы #tbl_jobs по условию IsAdd=1. Таким образом, выполнен перенос тех заданий на сервер-получатель, которых нет в таблице #tbl_notentity из п.1 алгоритма.

3) перенести шаги перенесенных заданий:

Запрос
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;

Сначала собираем все имеющиеся шаги заданий на сервере-получателе в таблицу #tbl_jobsteps. Затем с помощью инструкции MERGE производим слияние по полям [job_id] и [step_id] в эту таблицу всех недостающих шагов заданий с сервера-источника, которых нет в таблице #tbl_notentity из п.1 алгоритма. Вставленные строки помечаем как 1 в столбце IsAdd. И далее, добавляем все шаги заданий в таблицу [msdb].[dbo].[sysjobsteps] сервера-получателя из таблицы #tbl_jobsteps по условию IsAdd=1. Затем удаляем таблицу #tbl_jobsteps, т к далее она нам больше не нужна.

Таким образом, выполнен перенос всех шагов тех заданий на сервер-получатель, которых нет в таблице #tbl_notentity из п.1 алгоритма.

4) перенести расписания перенесенных заданий:

Запрос
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;

Сначала собираем все имеющиеся расписания на сервере-получателе в таблицу #tbl_sysschedules. Затем с помощью инструкции MERGE производим слияние по полю [schedule_uid] в эту таблицу всех недостающих расписаний с сервера-источника, которых нет в таблице #tbl_notentity из п.1 алгоритма. Вставленные строки помечаем как 1 в столбце IsAdd. И далее, добавляем все расписания в таблицу [msdb].[dbo].[sysschedules] сервера-получателя из таблицы #tbl_sysschedules по условию IsAdd=1. Затем удаляем таблицу #tbl_sysschedules, т к далее она нам больше не нужна.

Таким образом, выполнен перенос всех расписаний на сервер-получатель, которых нет в таблице #tbl_notentity из п.1 алгоритма.

5) перенести связку расписания-задания для перенесенных заданий:

Запрос
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;

Сначала собираем все имеющиеся связи расписания-задания на сервере-получателе в таблицу #tbl_jobschedules. Затем с помощью инструкции MERGE производим слияние по полям [job_id] и [schedule_uid] в эту таблицу всех недостающих связок с сервера-источника, которых нет в таблице #tbl_notentity из п.1 алгоритма. Вставленные строки помечаем как 1 в столбце IsAdd. И далее, добавляем все расписания в таблицу [msdb].[dbo].[sysjobschedules] сервера-получателя из таблицы #tbl_jobschedules по условию IsAdd=1. Затем удаляем таблицу #tbl_jobschedules, т к далее она нам больше не нужна.

Таким образом, выполнен перенос всех связок расписаний-заданий на сервер-получатель, которых нет в таблице #tbl_notentity из п.1 алгоритма.

6) перенести целевые сервера для перенесенных заданий:

Запрос
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;

Сначала собираем все имеющиеся связи задания-целевые сервера на сервере-получателе в таблицу #tbl_sysjobservers. Затем с помощью инструкции MERGE производим слияние по полям [job_id] и [server_id] в эту таблицу всех недостающих связок с сервера-источника, которых нет в таблице #tbl_notentity из п.1 алгоритма. Вставленные строки помечаем как 1 в столбце IsAdd. И далее, добавляем все связи в таблицу [msdb].[dbo].[sysjobservers] сервера-получателя из таблицы #tbl_sysjobservers по условию IsAdd=1. Затем удаляем таблицы #tbl_sysjobservers и #tbl_notentity, т к далее они нам больше не нужны.

Таким образом, выполнен перенос всех связок задания-целевые сервера на сервер-получатель, которых нет в таблице #tbl_notentity из п.1 алгоритма.

Важно отметить, что если в заданиях присутствуют целевые сервера, отличные от локального (т е идентификатор не равен нулю), то необходимо сначала перенести сами определения этих целевых серверов, а потом уже производить п.6 алгоритма.

7) регистрируем задания и активизируем их расписания, переведя в неактивный режим эти задания (выключением заданий)

и

8) назначаем владельца для всех перенесенных заданий (например, sa)

Запрос
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;

Сначала всем перенесенным заданиям назначаем владельца sa (определяем перенесенные задания по таблице #tbl_jobs). Затем производим регистрацию каждого перенесенного задания и активизируем их расписания с помощью вызова системной хранимой процедуры [msdb].[dbo].sp_update_job на сервере-получателе для выключения перенесенных заданий. И далее, удаляем таблицу #tbl_jobs, т к больше она не нужна.

Таким образом, всем перенесенным заданиям назначен владелец sa, и все эти задания были зарегистрированы (и активированы их расписания) через их выключение.
Далее необходимые задания нужно включить скриптом или вручную.

Приведем код всего скрипта:

Запрос
--собираем те задания, которые переносить не нужно
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;

Результат


В данной статье был рассмотрен пример реализации T-SQL скрипта, который позволяет перенести задания и расписания Агента с одного экземпляра MS SQL Server на другой. Также данный подход можно реализовать и с помощью других средств. Например, PowerShell или C#.

Источники:


» msdb
» SQL Server Agent Tables
» sp_update_job
Tags:
Hubs:
+6
Comments 12
Comments Comments 12

Articles