Как перенести job с одного sql сервера на другой

Перенос заданий Агента SQL Server в ADF с помощью SSMS

Область применения:Фабрика данных Azure Azure Synapse Analytics

Попробуйте использовать фабрику данных в Microsoft Fabric, решение для аналитики с одним интерфейсом для предприятий. Microsoft Fabric охватывает все, от перемещения данных до обработки и анализа данных в режиме реального времени, бизнес-аналитики и отчетности. Узнайте, как бесплатно запустить новую пробную версию !

Перенося локальные рабочие нагрузки SQL Server Integration Services (SSIS) в службы SSIS в ADF, можно после переноса пакетов служб SSIS выполнить пакетную миграцию заданий Агента SQL Server с типом шага задания, определенным как перенос пакета SQL Server Integration Services в конвейеры, действия или триггеры по расписанию Фабрики данных Azure (ADF), используя Мастер миграции заданий служб SSIS SQL Server Management Studio (SSMS).

Как правило, для выбранных заданий агента SQL с поддерживаемыми типами шагов заданий Мастер миграции заданий SSIS позволяет:

сопоставить локальное расположение пакета SSIS с расположением, куда будут переноситься пакеты, доступные SSIS в ADF;

Примечание. Поддерживаются только пакеты, располагающиеся в файловой системе.

Объект задания Агента SQL	Ресурс ADF	Примечания.
Задание Агента SQL	конвейер	Конвейеру будет присвоено имя Создан для .

• создать шаблоны Azure Resource Manager (ARM) в локальной выходной папке и развернуть их в Фабрике данных напрямую или позднее вручную (дополнительные сведения о шаблонах ADF Resource Manager см. на странице типов ресурсов Microsoft.DataFactory).

Необходимые компоненты

Для использования функции, описываемой в этой статье, требуется SQL Server Management Studio версии 18.5 или более поздней. Чтобы получить последнюю версию SSMS, перейдите на страницу скачивания SQL Server Management Studio (SSMS).

Миграция заданий SSIS в ADF

1. В обозревателе объектов SSMS выберите Агент SQL Server, далее выберите папку «Задания», а затем щелкните ее правой кнопкой мыши и выберите пункт Перенести задания SSIS в ADF.
2. Войдите в Azure и выберите подписку Azure, Фабрику данных и среду выполнения интеграции. Службу хранилища Azure выбирать необязательно — она используется на этапе сопоставления расположений пакетов, если у подлежащих переносу заданий служб SSIS есть пакеты SSIS в файловой системе.
3. Сопоставьте пути к пакетам SSIS и файлам конфигурации в заданиях SSIS с путями назначения, к которым могут получить доступ перенесенные конвейеры. На этом шаге сопоставления можно выполнить следующие действия:
   1. Выбрать исходную папку, а затем нажать кнопку Добавить сопоставление.
   2. Обновить путь к исходной папке. Допустимы пути к папкам пакетов или их родительским папкам.
   3. Обновить путь к конечной папке. Изначально используется относительный путь к учетной записи хранения по умолчанию, которая была выбрана на шаге 1.
   4. Удалить выбранное сопоставление, нажав кнопку Удалить сопоставление.
4. Выберите поддерживаемые задания для миграции и настройте параметры соответствующих действий «Выполнить пакет SSIS».
   • Параметры по умолчанию — это параметры, которые применяются по умолчанию ко всем выбранным шагам. Дополнительные сведения о каждом свойстве см. на вкладке Параметрыдействия «Выполнить пакет SSIS» в случае, когда расположение пакета задано как Файловая система (пакет).
   • Параметры шага — настройка параметров выбранного шага. Применить параметры по умолчанию — изначально этот флажок установлен. Снимите его, чтобы настроить параметры только для выбранного шага.
     Дополнительные сведения о других свойствах см. на вкладке Параметрыдействия «Выполнить пакет SSIS» в случае, когда расположение пакета задано как Файловая система (пакет).
5. Создайте и разверните шаблон ARM.
   1. Выберите или введите выходной путь к шаблонам ARM перенесенных конвейеров ADF. Если папки не существует, она будет создана автоматически.
   2. Выберите вариант развертывания шаблонов ARM в фабрике данных:
      • По умолчанию он снят, и созданные шаблоны ARM можно развернуть позже вручную.
      • Чтобы сразу развернуть созданные шаблоны ARM в фабрике данных, установите этот флажок.
6. Выполните миграцию, а затем проверьте результаты.

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

Довольно часто бывает необходимо перенести задания Агента на другой экземпляр 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 ( ) 

Таким образом, получили таблицу непереносимых заданий #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 ( ) --переносим задания 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#.

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

Спасибо!
Как раз ищем приемлимый способ синхронизировать джобы на alwayson кластере, пока находил только плагин под студию для ручной синхронизации.

Всего голосов 1: ↑1 и ↓0 +1
Ответить Добавить в закладки Ещё
Показать предыдущий комментарий
Посмотрите dbatools.io
Всего голосов 1: ↑1 и ↓0 +1
Ответить Добавить в закладки Ещё
Показать предыдущий комментарий

Спасибо за ссылку-интересный ресурс-поюзаю (особенно понравились заявленные возможности Copy-DbaAgentJob и Copy-DbaDatabaseMail)

Комментарий пока не оценивали 0
Ответить Добавить в закладки Ещё
Показать предыдущий комментарий
Спасибо, тоже интересный вариант
Всего голосов 1: ↑1 и ↓0 +1
Ответить Добавить в закладки Ещё
Показать предыдущий комментарий

Вы стимулируете писать больше)
Как раз ради таких комментариев я по большей части и пишу, чтобы узнать другой опыт и другие инструменты, подходы и т д

Комментарий пока не оценивали 0
Ответить Добавить в закладки Ещё

Коллеги, гораздо проще создать SSIS пакет, тем более у вас уже есть 17 версия. Там есть уже готовые таски Transfer Database, Transfer Jobs, Transfer Logins, Transfer SQL Server Objects ets…
Указываешь сорс и дестинейшен, немного параметров (типа — перезаписывать если уже есть такое задание) и запускаешь. Можно опубликовать пакет и запускать из джоба, где в параметрах запуска можно указывать разные инстансы, получается достаточно универсально и быстро.

Всего голосов 1: ↑1 и ↓0 +1
Ответить Добавить в закладки Ещё

А я object browser открывал, галочка и все выделял, и давил капу «script». Но для кейса автоматизированного переноса ваш способ действительно хорош. Главное ещё как-то распознать, какие из заданий надо оставить активными, а какие нет.

Комментарий пока не оценивали 0
Ответить Добавить в закладки Ещё
Показать предыдущий комментарий

Вот каждый раз и надоело Галочкин ставить и скрипт генерировать)
Здесь же вначале просто определиться что не переносить, а задачи обычно все сначала выключены перенесенные, т к перед включением нужно будет до настроить БД (напр, указать новый путь для создания резервных копий и т д)

Комментарий пока не оценивали 0
Ответить Добавить в закладки Ещё
Спасибо, очень помогло ваше описание. Сегодня только вот занимался.
Всего голосов 1: ↑1 и ↓0 +1
Ответить Добавить в закладки Ещё

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

Комментарий пока не оценивали 0
Ответить Добавить в закладки Ещё

Спасибо за Ваш труд, материал действительно полезен!
Единственный минус, что нужен прямой доступ к серверу назначения. Чтобы заполнить этот пробел, советую использовать универсальное приложение ImportExportDataSql по выгрузке результатов запросов в различные форматы (CSV, SQL). Все записи выгружаем в файл формата SQL и прогоняем его на любой машине, куда нужно перенести джобы.
Писал утилиту для себя, чтобы переносить данные с тестового сервера на боевой.
Бесплатная, портативная (не требует установки), без рекламы, оповещает о новых версиях.
Подробное описание ImportExportDataSql

Пример выгрузки джобов:

Всего голосов 1: ↑1 и ↓0 +1
Ответить Добавить в закладки Ещё
Показать предыдущий комментарий

Большое спасибо за Ваше решение!

Как получить скрипт для всех джобов на MS SQL Server

Иногда случается, что надо перенести джобы с одного сервера на другой. Или сохранить джобы в качестве скрипта и положить в репозиторий. Если у нас всего два-три джоба, то можно использовать «Script Job As» >> «CREATE To» >> «. выберите куда. «. Но что если у нас десятки джобов?

Решение

1. Открываем Management Studio.
2. Выбираем SQL Server Agent — Jobs.
3. Запускаем Object Explorer Details (нажмите F7 или в верхнем меню View — Object Explorer Details).
4. Выделяем нужные джобы (или все, тогда просто нажимаем CTRL+A).
5. Правой кнопкой мыши на выделенной строчке вызываем контекстное меню и далее привычный «Script Job As».

Полученный скрипт вы можете выполнить на другом сервере, положить в репозиторий или отредактировать. Также допустимы другие массовые действия с джобами, например, удаление.