--..譟磯 觜′碁.. 2螳 蟇碁語..
set nocount on
set statistics io off
declare @sql nvarchar(max)
--job category
set @sql = convert(nvarchar(max), '
declare @new_job_id binary(16)
--job category
if not exists (select * from msdb.dbo.syscategories where name = N''[uncategorized (local)]'' and category_class = 1)
begin
exec msdb.dbo.sp_add_category
@class = N''JOB''
, @type = N''LOCAL''
, @name = N''[Uncategorized (Local)]''
end
')
print @sql
declare
@job_name sysname
, @enabled tinyint
, @notify_level_eventlog int
, @notify_level_email int
, @notify_level_netsend int
, @notify_level_page int
, @delete_level int
, @description nvarchar(512)
, @category_name sysname
, @owner_login_name sysname
, @job_id binary(16)
, @step_name sysname
, @step_id int
, @cmdexec_success_code int
, @on_success_action tinyint
, @on_success_step_id int
, @on_fail_action tinyint
, @on_fail_step_id int
, @retry_attempts int
, @retry_interval int
, @os_run_priority int
, @subsystem nvarchar(40)
, @command nvarchar(max)
, @database_name sysname
, @flags int
, @start_step_id int
, @schedule_name sysname
, @f_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
, @server_name sysname
declare cur cursor for
select
a.name job_name
, a.enabled
, a.notify_level_eventlog
, a.notify_level_email
, a.notify_level_netsend
, a.notify_level_page
, a.delete_level
, a.description
, b.name category_name
, c.loginname owner_login_name
, a.job_id
, a.start_step_id
, f.name schedule_name
, f.enabled
, f.freq_type
, f.freq_interval
, f.freq_subday_type
, f.freq_subday_interval
, f.freq_relative_interval
, f.freq_recurrence_factor
, f.active_start_date
, f.active_end_date
, f.active_start_time
, f.active_end_time
, case when i.server_name = @@servername then N'(local)' else i.server_name end server_name
from msdb.dbo.sysjobs a
inner join msdb.dbo.syscategories b
on a.category_id = b.category_id
inner join sys.syslogins c
on a.owner_sid = c.sid
inner join msdb.dbo.sysjobschedules e
on a.job_id = e.job_id
inner join msdb.dbo.sysschedules f
on e.schedule_id = f.schedule_id
inner join msdb.dbo.sysjobservers g
on a.job_id = g.job_id
inner join msdb.dbo.sysjobservers h
on a.job_id = h.job_id
left join msdb.dbo.systargetservers_view i
on h.server_id = i.server_id
open cur;
fetch next from cur into
@job_name
, @enabled
, @notify_level_eventlog
, @notify_level_email
, @notify_level_netsend
, @notify_level_page
, @delete_level
, @description
, @category_name
, @owner_login_name
, @job_id
, @start_step_id
, @schedule_name
, @f_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
, @server_name;
while @@FETCH_STATUS not in (-1, -2)
begin
print ' -----------------------------------------------------------------'
print ' -- Job name : ' + @job_name
print ' -----------------------------------------------------------------'
--job
set @sql = convert(nvarchar(max), '
exec msdb.dbo.sp_add_job
@job_name = N''' + @job_name + '''
, @enabled = ' + convert(varchar, @enabled) + '
, @notify_level_eventlog = ' + convert(varchar, @notify_level_eventlog) + '
, @notify_level_email = ' + convert(varchar, @notify_level_email) + '
, @notify_level_netsend = ' + convert(varchar, @notify_level_netsend) + '
, @notify_level_page = ' + convert(varchar, @notify_level_page) + '
, @delete_level = ' + convert(varchar, @delete_level) + '
, @description = N''' + @description + '''
, @category_name = N''' + @category_name + '''
, @owner_login_name = N''' + @owner_login_name + '''
, @job_id = @new_job_id output
')
print @sql;
--job step
declare inner_cur cursor for
select
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
, replace(command, '''', '''''')
, database_name
, flags
from msdb.dbo.sysjobsteps
where job_id = @job_id
open inner_cur;
fetch next from inner_cur into
@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;
while @@FETCH_STATUS not in (-1, -2)
begin
--job step
print ' -----------------------------------------------------------------'
print ' --job step' + convert(varchar, @step_id) + ' : ' + @step_name
print ' -----------------------------------------------------------------'
set @sql = convert(nvarchar(max), '
EXEC msdb.dbo.sp_add_jobstep
@job_id = @new_job_id
, @step_name = N''' + @step_name + '''
, @step_id = ' + convert(varchar, @step_id) + '
, @cmdexec_success_code = ' + convert(varchar, @cmdexec_success_code) + '
, @on_success_action = ' + convert(varchar, @on_success_action) + '
, @on_success_step_id = ' + convert(varchar, @on_success_step_id) + '
, @on_fail_action = ' + convert(varchar, @on_fail_action) + '
, @on_fail_step_id = ' + convert(varchar, @on_fail_step_id) + '
, @retry_attempts = ' + convert(varchar, @retry_attempts) + '
, @retry_interval = ' + convert(varchar, @retry_interval) + '
, @os_run_priority = ' + convert(varchar, @os_run_priority) + '
, @subsystem = N''' + @subsystem + '''
, @command = N''' + @command + '''
, @database_name = N''' + @database_name + '''
, @flags = ' + convert(varchar, @flags))
print @sql
fetch next from inner_cur into
@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;
end
close inner_cur;
deallocate inner_cur;
--start step job
set @sql = convert(nvarchar(max), '
--start step job
exec msdb.dbo.sp_update_job
@job_id = @new_job_id
, @start_step_id = ' + convert(varchar, @start_step_id))
print @sql
--job schedule
set @sql = convert(nvarchar(max), '
--job schedule
exec msdb.dbo.sp_add_jobschedule
@job_id = @new_job_id
, @name = N''' + @schedule_name + '''
, @enabled = ' + convert(varchar, @f_enabled) + '
, @freq_type = ' + convert(varchar, @freq_type) + '
, @freq_interval = ' + convert(varchar, @freq_interval) + '
, @freq_subday_type = ' + convert(varchar, @freq_subday_type) + '
, @freq_subday_interval = ' + convert(varchar, @freq_subday_interval) + '
, @freq_relative_interval = ' + convert(varchar, @freq_relative_interval) + '
, @freq_recurrence_factor = ' + convert(varchar, @freq_recurrence_factor) + '
, @active_start_date = ' + convert(varchar, @active_start_date) + '
, @active_end_date = ' + convert(varchar, @active_end_date) + '
, @active_start_time = ' + convert(varchar, @active_start_time) + '
, @active_end_time = ' + convert(varchar, @active_end_time))
print @sql
--job server
set @sql = convert(nvarchar(max), '
--job server
exec msdb.dbo.sp_add_jobserver
@job_id = @new_job_id,
@server_name = N''' + @server_name + '''
')
print @sql
fetch next from cur into
@job_name
, @enabled
, @notify_level_eventlog
, @notify_level_email
, @notify_level_netsend
, @notify_level_page
, @delete_level
, @description
, @category_name
, @owner_login_name
, @job_id
, @start_step_id
, @schedule_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
, @server_name;
end
close cur;
deallocate cur;