博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
Generate the Jobs script from msdb Database
阅读量:5806 次
发布时间:2019-06-18

本文共 11004 字,大约阅读时间需要 36 分钟。

前两周,由于数据库简繁体的转换,大量的数据库需要转到新的服务器。 在转其中的一台的时候,原先导出来的JOBS脚本不翼而飞(原因至今未明),而恰巧这一台服务器有90多个JOB(看下图恢复后的,注意滚动条的长短),这样一来,可急坏了人了。 这么多的JOB要一个一个建立,时间去了不说,有些JOB根本不知道执行的是哪些命令,执行顺序如何,这么多的系统相互交纵复杂,如果不能正确的处理好,星期一可就乱了套了。到时谁都不会有好果子吃。 由于JOBS都是存在于msdb数据库,还好,我们平时是有备份这个数据库的,这样一来,我们应该想办法从这个数据库里取回一些有用的信息。 首先,我应该恢复一个msdb数据库为别的名字,然后赶紧求助于互联网。经过了一番摸索终于可以从该数据库中提取出脚本。 现在把这脚本也放出来,希望有此需要的人做参考使用,亦或大家做学习之用,如果大家有好的方法也请讲出。 当然,要是在系统完好之前,直接备份就用不着这么麻烦了,直接在图形介面产生的脚本又快又准确。

 

CREATE  PROCEDURE  Get_JobListas    --  created by geton on 2014-08-16    declare @jobnamecc  varchar(250)    declare @strsql  varchar(5000)    declare @jobname varchar(30),@category_calss_i int,@category_calss varchar(50),@category_name varchar(50),        @category_type varchar(30),@category_id int,@category_type_i int            declare joblist cursor for select [name]  from     msdb.dbo.sysjobs --where name='HR - AUTO GET COE' open joblist    fetch next from joblist into @jobnamecc      while @@fetch_status=0           begin              begin try               select @jobname =@jobnamecc ,@category_calss = '',@category_name='',@category_type = ''                        select                 @category_calss = case when tshc.category_class = 1 then 'JOB'                                           when tshc.category_class = 2 then 'ALERT'                                           else 'OPERATOR'                                           end,                @category_type = case when tshc.category_type = 1 then 'LOCAL'                                          when tshc.category_type = 2 then 'MULTI-SERVER'                                          else 'NONE'                                          end,                     @category_name = tshc.name,                    @category_type_i = category_type,                    @category_calss_i = tshc.category_class,                    @category_id = tshc.category_id                    from                    msdb.dbo.sysjobs_view as sv    -- Here "msdb.dbo.sysjobs_view" should be changed to the DB which you restored                INNER JOIN msdb.dbo.syscategories  as tshc on sv.category_id = tshc.category_id                  where    -- Here "msdb.dbo.syscategories" should be changed to the DB which you restored                 (sv.name=@jobname AND tshc.category_class = 1)   --  category_class = 1 -- only job type are exported        PRINT 'BEGIN TRANSACTION'        PRINT 'DECLARE @ReturnCode INT'       PRINT 'SELECT @ReturnCode = 0'      PRINT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''' + @category_name +'''AND category_class=' +rtrim(@category_calss_i)+')'        PRINT 'BEGIN'        PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'''+ @category_calss+''', @type=N'''+@category_type+''', @name=N'''+@category_name+''''        PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'        PRINT 'end'            declare @eventloglevel int,@emaillevel int,@netsendlevel int,@pagelevel int        declare @emailleveloprid nvarchar(256),@netsendleveloprid nvarchar(256),@pageleveloprid nvarchar(256)        declare @isenable int , @description nvarchar(1024),@owner_log_name nvarchar(512),@delete_level int        declare @jobid uniqueidentifier,@start_step_id int,@server nvarchar(512)        select        @eventloglevel=sv.notify_level_eventlog,        @emaillevel=sv.notify_level_email ,        @netsendlevel=sv.notify_level_netsend,         @pagelevel=sv.notify_level_page,         @emailleveloprid = isnull((select top 1 name from   msdb..sysoperators where id = sv.notify_email_operator_id),''),        @netsendleveloprid  =  isnull((select top 1 name from   msdb..sysoperators where id = sv.notify_netsend_operator_id),''),        @pageleveloprid = isnull((select top 1 name from   msdb..sysoperators where id = sv.notify_page_operator_id),'') ,       @isenable = sv.enabled ,       @description = sv.description ,       @owner_log_name = isnull(suser_sname(sv.owner_sid), N''''),      @delete_level = sv.delete_level,        @jobid = sv.job_id,        @start_step_id = start_step_id ,       @server = '(local)'--originating_server  --Due to this script would be executed on target machine,Here change the @server parameter to "LOCAL"     from msdb.dbo.sysjobs_view as sv        where (sv.name=@jobname )            --  select * from msdb.dbo.sysjobs_view    PRINT 'DECLARE @jobId BINARY(16)'        PRINT 'EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'''+@jobname+''','        PRINT ' @enabled='+RTRIM(@isenable)+', '        PRINT ' @notify_level_eventlog='+RTRIM(@eventloglevel)+', '        PRINT ' @notify_level_email='+RTRIM(@emaillevel)+', '        PRINT ' @notify_level_netsend='+RTRIM(@netsendlevel)+', '        PRINT ' @notify_level_page='+RTRIM(@pagelevel)+', '        PRINT ' @notify_email_operator_name ='''+RTRIM(@emailleveloprid)+''', '        PRINT ' @notify_netsend_operator_name='''+RTRIM(@netsendleveloprid)+''', '        PRINT ' @notify_page_operator_name='''+RTRIM(@pageleveloprid)+''', '        PRINT ' @delete_level='+RTRIM(@delete_level)+', '        PRINT ' @description=N'''+@description+''', '        PRINT ' @category_name=N'''+@category_name+''', '        PRINT ' @owner_login_name=N'''+@owner_log_name+''', '        PRINT ' @job_id = @jobId OUTPUT'        PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'         --SELECT * FROM msdb.dbo.syscategories         declare @step_id int        declare @step_name nvarchar(512) ,@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(512),@database_name nvarchar(512),@flags int,@command nvarchar(max)            declare jbcur cursor  for      select step_id  from msdb..sysjobsteps  where job_id = @jobid order by step_id ;      --  "msdb..sysjobsteps"  should be changed to the DB which you restored    open jbcur;        fetch next from jbcur into @step_id        while @@fetch_status = 0        begin                      SELECT        @step_name = step_name,           @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,           @database_name = database_name,           @command = command,           @flags = flags         FROM  msdb..sysjobsteps a WHERE job_id = @jobid and step_id  = @step_id                    PRINT ' EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, '         PRINT ' @step_name=N'''+@step_name+''', '         PRINT ' @step_id='+RTRIM(@step_id)+', '         PRINT ' @cmdexec_success_code='+RTRIM(@cmdexec_success_code)+', '         PRINT ' @on_success_action='+RTRIM(@on_success_action)+', '         PRINT ' @on_success_step_id='+RTRIM(@on_success_step_id)+', '         PRINT ' @on_fail_action='+RTRIM(@on_fail_action)+', '         PRINT ' @on_fail_step_id='+RTRIM(@on_fail_step_id)+', '         PRINT ' @retry_attempts='+RTRIM(@retry_attempts)+', '         PRINT ' @retry_interval='+RTRIM(@retry_interval)+', '         PRINT ' @os_run_priority='+RTRIM(@os_run_priority)+', @subsystem=N'''+@subsystem+''', '         PRINT ' @database_name=N'''+@database_name+''','         PRINT ' @flags='+RTRIM(@flags)+' ,'         PRINT ' @command=N'''+REPLACE(@command,'''','''''')+''''         PRINT ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'            fetch next from jbcur into @step_id            end            close jbcur        deallocate jbcur                   PRINT '    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = '+rtrim(@start_step_id)        PRINT '    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback    '            DECLARE @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,@name VARCHAR(512)            SELECT         @name = a.name,       @enabled = enabled ,       @freq_interval = freq_interval ,        @freq_type = freq_type  ,       @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 msdb..sysschedules a   -- "msdb..sysschedules"  should be changed to the DB which you restored     INNER JOIN msdb.dbo.sysjobschedules b ON a.schedule_id = b.schedule_id  -- "msdb..sysjobschedules " should be changed to the DB which you restored    WHERE  job_id = @jobid                IF(@name IS not null)        begin         PRINT '    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'''+@name+''', '         PRINT '    @enabled='+RTRIM(@enabled)+', '         PRINT '    @freq_type='+RTRIM(@freq_type)+', '         PRINT '    @freq_interval='+RTRIM(@freq_interval)+', '         PRINT '    @freq_subday_type='+RTRIM(@freq_subday_type)+', '         PRINT '    @freq_subday_interval='+RTRIM(@freq_subday_interval)+', '         PRINT '    @freq_relative_interval='+RTRIM(@freq_relative_interval)+', '         PRINT '    @freq_recurrence_factor='+RTRIM(@freq_recurrence_factor)+', '         PRINT '    @active_start_date='+RTRIM(@active_start_date)+', '         PRINT '    @active_end_date='+RTRIM(@active_end_date)+', '         PRINT '    @active_start_time='+RTRIM(@active_start_time)+', '         PRINT '    @active_end_time='+RTRIM(@active_end_time)+' '         --PRINT '    @schedule_uid=N'''+RTRIM(NEWID())+''''         PRINT ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'        end                    PRINT ' EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'''+@server+''''        PRINT ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'        PRINT 'COMMIT TRANSACTION'        PRINT 'GOTO endSave'        PRINT 'QuitWithRollback:'        PRINT 'IF(@@TRANCOUNT>0)ROLLBACK TRANSACTION'        PRINT 'endSave:'        PRINT ' '        PRINT 'GO'                             --PRINT @strsql           exec (@strsql)         end try       begin catch             declare @errmsg varchar(1000)            set @errmsg='[Prco_Get_IPDataBase]:'+ERROR_MESSAGE()            print @errmsg            print @strsql      end catch;            fetch next from joblist into @jobnamecc          end       close joblist       deallocate  joblist

 

转载于:https://www.cnblogs.com/Geton/p/3957475.html

你可能感兴趣的文章
StringBuffer的解读(二)
查看>>
以太坊行情预测 kinmall:以太币能否带来新一轮的牛市?
查看>>
linux vsftpd 配置 常用
查看>>
一文告诉你SD-WAN与MPLS的区别在哪里?
查看>>
如何用 RocketMQ 打造金融级消息服务平台?微众银行这么做
查看>>
Android 升级数据库的最佳写法
查看>>
真的来了!首批科创板IPO受理企业名单出炉
查看>>
Python 编程语言实行尽可能成熟、稳定的新管理模式
查看>>
Bootstrap中datetimepicker日期控件1899年问题解决
查看>>
Dubbo Metrics 发布新版本 2.0.1 | Dubbo 的度量统计基础设施
查看>>
第四篇:SpringBoot 2.x整合MyBatis
查看>>
Visual Studio安装SVN插件
查看>>
JS如何模拟鼠标点击X,Y坐标
查看>>
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 28 章 监控数据库活动_28.2. 统计收集器...
查看>>
Vue 使用
查看>>
Java技术大牛成就之路的故事
查看>>
运动的意义
查看>>
Top 7大开源数据可视化分析工具!
查看>>
Vue2+VueRouter2+webpack 构建项目
查看>>
NPOI处理Word文本中上下角标
查看>>