#title Job Script
[[TableOfContents]]

==== 언제 써먹나? ====
 * SQL Server 2005 -> 2008
 * 서버 옮길때..

==== 주의사항 ====
 * 아직 테스트 안함.. ㅋ
 * ssis package 호출하는 job은 패스워드를 한 번 입력해야 하나?

==== Job Script 뽑는데 필요한 데이터 ====
{{{
	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
	,	d.step_name
	,	d.step_id
	,	d.cmdexec_success_code
	,	d.on_success_action
	,	d.on_success_step_id
	,	d.on_fail_action
	,	d.on_fail_step_id
	,	d.retry_attempts
	,	d.retry_interval
	,	d.os_run_priority
	,	d.subsystem
	,	d.command
	,	d.database_name
	,	d.flags
	,	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.sysjobsteps d
			on a.job_id = d.job_id
		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
}}}
==== Script ====
{{{
--헉헉..조닌 빡세다.. 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;
}}}