Перенос JOB`ов и DTS-пакетов с MS SQL Server 2000 на MS SQL Server 2005/2008 

В очередной раз получив вопрос, как быстро перенести большой объём Job`ов и DTS-пакетов с MS SQL Server 2000 на MS SQL Server 2005/2008, решил выложить скрипт, который я писал в своё время при переносе БД на, ещё тогда, новый SQL Server 2005. Может кому-то ещё он будет полезен.

USE msdb

DECLARE @LinkedServ NVARCHAR(128)

SET @LinkedServ=N'MyLinkedServer2000'--Linked Server!!!

('

FROM '+@LinkedServ+'.msdb.dbo.sysjobs

')

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

('

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 )

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 id=@i

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

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

('

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

SET @i=1

WHILE @i<=(SELECT COUNT(*) FROM #tempSchedules)

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 id=@i

EXEC msdb.dbo.sp_add_jobschedule

@job_id=@jobId,

@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

SET @i=@i+1

DROP TABLE #tempSchedules

('

from '+@LinkedServ+'.msdb.dbo.sysjobservers

')

INSERT INTO msdb.dbo.sysdtspackages

SELECT [name]

,[id]

,[versionid]

,[description]

,[categoryid]

,[createdate]

,[owner]

,[packagedata]

,[owner_sid]

,0 FROM MyLinkedServer2000.msdb.dbo.sysdtspackages

Опубликовано 20 января 2009 г. 17:29 в MSSQL

2

Просмотров: 2894

Ответов: 0