Перенос заданий и расписаний с одного экземпляра 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) собираем те задания, которые переносить не нужно:

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

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

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

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

Сначала собираем все имеющиеся шаги заданий на сервере-получателе в таблицу #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) перенести расписания перенесенных заданий:

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

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

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

Сначала собираем все имеющиеся связи расписания-задания на сервере-получателе в таблицу #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) перенести целевые сервера для перенесенных заданий:

Сначала собираем все имеющиеся связи задания-целевые сервера на сервере-получателе в таблицу #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)

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

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

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

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