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

Перенос заданий и расписаний с одного экземпляра 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#.

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

use msdb
Declare @LinkedServ NVarchar(128)
set @LinkedServ=N’MATRIX’—Linked Server.

Create Table # tempSteps
(
id int identity
,job_ID uniqueidentifier
,step_name NVarchar(128)
,step_id int
,cmdexec_success_code int
,on_success_action int
,on_success_step_id int
,on_fail_action int
,on_fail_step_id int
,retry_attempts int
,retry_interval int
,os_run_priority int
,subsystem NVARCHAR(40)
,command NVARCHAR(max)
,database_name NVARCHAR(128)
,flags int
)

exec
(‘
insert into # tempSteps select
job_ID
,step_name
,step_id
,cmdexec_success_code
,on_success_action
,on_success_step_id
,on_fail_action
,on_fail_step_id
,retry_attempts
,retry_interval
,os_run_priority
,subsystem
,command
,database_name
,flags
FROM ‘+@LinkedServ+’.msdb.dbo.sysjobsteps
‘)

declare @job_ID uniqueidentifier
declare @step_name NVarchar(128)
declare @step_id int
declare @cmdexec_success_code int
declare @on_success_action int
declare @on_success_step_id int
declare @on_fail_action int
declare @on_fail_step_id int
declare @retry_attempts int
declare @retry_interval int
declare @os_run_priority int
declare @subsystem NVARCHAR(40)
declare @command NVARCHAR(max)
declare @database_name NVARCHAR(128)
declare @flags int

declare @i int
set @i=1
while @i<=(select count(*) from # tempSteps )
begin

select
@job_ID =job_ID
,@step_name =step_name
,@step_id =step_id
,@cmdexec_success_code =cmdexec_success_code
,@on_success_action =on_success_action
,@on_success_step_id=on_success_step_id
,@on_fail_action =on_fail_action
,@on_fail_step_id =on_fail_step_id
,@retry_attempts =retry_attempts
,@retry_interval =retry_interval
,@os_run_priority =os_run_priority
,@subsystem =subsystem
,@command =command
,@database_name =database_name
,@flags =flags
FROM # tempSteps where >

EXEC msdb.dbo.sp_add_jobstep
@job_id=@job_id,
@step_name=@step_name,
@step_id=@step_id,
@cmdexec_success_code=@cmdexec_success_code,
@on_success_action=@on_success_action,
@on_success_step_id=@on_success_step_id,
@on_fail_action=@on_fail_action,
@on_fail_step_id=@on_fail_step_id,
@retry_attempts=@retry_attempts,
@retry_interval=@retry_interval,
@os_run_priority=@os_run_priority,
@subsystem=@subsystem,
@command=@command,
@database_name=@database_name,
@flags=@flags

set @i=@i+1
end
drop table # tempSteps

Create Table #tempSchedules
(
id int identity
,job_ID uniqueidentifier
,Name NVarchar(128)
,enabled int
,freq_type int
,freq_interval int
,freq_subday_type int
,freq_subday_interval int
,freq_relative_interval int
,freq_recurrence_factor int
,active_start_date int
,active_end_date int
,active_start_time int
,active_end_time int
)

Читать:

Как в excel определить пол по фио

exec
(‘
insert into #tempSchedules select
job_id
,name
,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
FROM ‘+@LinkedServ+’.msdb.dbo.sysjobschedules
‘)

declare @JobID uniqueidentifier
declare @Name NVarchar(128)
declare @enabled int
declare @freq_type int
declare @freq_interval int
declare @freq_subday_type int
declare @freq_subday_interval int
declare @freq_relative_interval int
declare @freq_recurrence_factor int
declare @active_start_date int
declare @active_end_date int
declare @active_start_time int
declare @active_end_time int

—declare @i int
set @i=1
while @i<=(select count(*) from #tempSchedules)
begin

select
@JobID=job_id
,@Name =name
,@enabled =enabled
,@freq_type =freq_type
,@freq_interval =freq_interval
,@freq_subday_type =freq_subday_type
,@freq_subday_interval =freq_subday_interval
,@freq_relative_interval= freq_relative_interval
,@freq_recurrence_factor =freq_recurrence_factor
,@active_start_date =active_start_date
,@active_end_date =active_end_date
,@active_start_time =active_start_time
,@active_end_time=active_end_time
FROM #tempSchedules where >

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

Либо, если джобов оч. много и скриптовать всё нет времени и перенос БД msdb не подходит,

то вот скриптик, который я писал для импорта:

use msdb
Declare @LinkedServ NVarchar(128)
set @LinkedServ=N’MATRIX’—Linked Server.

declare @i int
set @i=1
while @i<=(select count(*) from # tempSteps )
begin

set @i=@i+1
end
drop table # tempSteps

—declare @i int
set @i=1
while @i<=(select count(*) from #tempSchedules)
begin

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

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I had been tasked with taking a backup of a SQL database and restoring it on a new SQL server I just setup. Easy enough, I told them. But now, I’ve just found out that the development team needs all of the logins, services and jobs ported over as well. Pretty much, a everything that SQL Server Agent and some of their special domain accounts handle. Can someone point me to some good articles or in the right direction as to how to accomplish this?

Thank you so much in advance!

Answers

This is my favorite script for copying Logins/permissions from one SQL instance to another:
Scripting Out the Logins, Server Role Assignments, and Server Permissions

One easy way of scripting out Jobs is to highlight all of them at once in Object Explorer Details of SSMS, then > right-click > Script Job as > Create to:

Then, you can create script to a file and run script in the new server instance to create jobs there.

Hope that helps,

Phil Streiff, MCDBA, MCITP, MCSA

Edited by philfactor Saturday, March 25, 2017 12:45 PM
Marked as answer by guesthost Saturday, March 25, 2017 1:32 PM
Unmarked as answer by guesthost Saturday, March 25, 2017 1:38 PM
Marked as answer by guesthost Monday, April 10, 2017 3:05 PM

All replies

Is there any SSIS jobs ?are using any linked server ?

SSIS job—> 1)Package deployed in MSDB — right click and choose export package.you can can shave it locally as file and deploy to another server.

2) Local file you need copy and paste in another server.

Note :- You need and change connection SSIS job after transfer using BIDS.Check you are running using proxy account,then create proxy account also.

Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

Edited by AV111 Saturday, March 25, 2017 4:27 AM SSIS packages

You’ve Transfer Jobs Task and Transfer Logins Task available in SSIS for this purpose

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
—————————-
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

This is my favorite script for copying Logins/permissions from one SQL instance to another:
Scripting Out the Logins, Server Role Assignments, and Server Permissions

One easy way of scripting out Jobs is to highlight all of them at once in Object Explorer Details of SSMS, then > right-click > Script Job as > Create to:

Then, you can create script to a file and run script in the new server instance to create jobs there.

Hope that helps,

Phil Streiff, MCDBA, MCITP, MCSA

Edited by philfactor Saturday, March 25, 2017 12:45 PM
Marked as answer by guesthost Saturday, March 25, 2017 1:32 PM
Unmarked as answer by guesthost Saturday, March 25, 2017 1:38 PM
Marked as answer by guesthost Monday, April 10, 2017 3:05 PM

This is ALL very exciting, but admittedly scary stuff! 🙂

I’m a newbie, tasked with migrating a critical prod DB from a 2005 mirror with Mixed Authentication and a boatload of jobs, logins and permissions, to my new "simple" 2016 EE server that I setup with just Windows Authentication.

The 2005 mirror has several users DBs that I will NOT be moving over, just one of them. So I guess my first task is to determine which jobs, logins and associated roles/permissions belong to just that single database, correct?

Thanks for helping me through these times experts — I don’t know what I’d do without you!

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

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

Решение

Результат

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

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

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

Answered by:

Question

Answers

All replies

Как перенести html сайт на wordpress

Как перенести joomla на другой телефон

Похожие статьи