Предисловие
Довольно часто бывает необходимо перенести задания Агента на другой экземпляр 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
Комментарии (8)
sgt-Awesome
30.10.2017 13:57+1Коллеги, гораздо проще создать SSIS пакет, тем более у вас уже есть 17 версия. Там есть уже готовые таски Transfer Database, Transfer Jobs, Transfer Logins, Transfer SQL Server Objects ets…
Указываешь сорс и дестинейшен, немного параметров (типа — перезаписывать если уже есть такое задание) и запускаешь. Можно опубликовать пакет и запускать из джоба, где в параметрах запуска можно указывать разные инстансы, получается достаточно универсально и быстро.
kolu4iy
30.10.2017 22:24А я object browser открывал, галочка и все выделял, и давил капу "script". Но для кейса автоматизированного переноса ваш способ действительно хорош. Главное ещё как-то распознать, какие из заданий надо оставить активными, а какие нет...
jobgemws Автор
30.10.2017 22:31Вот каждый раз и надоело Галочкин ставить и скрипт генерировать)
Здесь же вначале просто определиться что не переносить, а задачи обычно все сначала выключены перенесенные, т к перед включением нужно будет до настроить БД (напр, указать новый путь для создания резервных копий и т д)
MagicEx
Спасибо!
Как раз ищем приемлимый способ синхронизировать джобы на alwayson кластере, пока находил только плагин под студию для ручной синхронизации.
tubecleaner
Посмотрите dbatools.io
jobgemws Автор
Спасибо за ссылку-интересный ресурс-поюзаю (особенно понравились заявленные возможности Copy-DbaAgentJob и Copy-DbaDatabaseMail)
MagicEx
Спасибо, тоже интересный вариант
jobgemws Автор
Вы стимулируете писать больше)
Как раз ради таких комментариев я по большей части и пишу, чтобы узнать другой опыт и другие инструменты, подходы и т д