| _覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
|
FrontPage › JobScript
|
|
|
[edit]
3 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 [edit]
4 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;
鏤
|
襾碁 螻 螳. 螳曙 ｴ 覦レ 讌襷 ′朱り 貅譽覃 覃襴 . (襦 貉) |