[MSSQL] 에이전트잡(AgentJob) 스크립트 추출

SQL Server의 GUI를 바탕으로 AgentJob스크립트를 생성하는게 가능하지만

스크립트로 Export하는 쿼리를 작성해보았다.

 

테스트는 SQL Server 2012, 2019에서 실행

SQL Server 2008 R2이상 버전에서 사용 가능

USE msdb
GO
 
DECLARE @name                        NVARCHAR(100)
DECLARE @enabled                    NVARCHAR(10)
DECLARE @notify_level_eventlog        NVARCHAR(10)
DECLARE @notify_level_email            NVARCHAR(10)
DECLARE @notify_level_page            NVARCHAR(10)
DECLARE @notify_level_netsend        NVARCHAR(10)
DECLARE @delete_level                NVARCHAR(10)
DECLARE @description                NVARCHAR(100)
DECLARE @category_class                NVARCHAR(20)
DECLARE @category_type                NVARCHAR(20)
DECLARE @sc_name                    NVARCHAR(100)
DECLARE @owner_sid                    NVARCHAR(50)
DECLARE @step_name                    NVARCHAR(50)
DECLARE @step_id                    NVARCHAR(20)
DECLARE @cmdexec_success_code        NVARCHAR(20)
DECLARE @on_success_action            NVARCHAR(20)
DECLARE @on_success_step_id            NVARCHAR(20)
DECLARE @on_fail_action                NVARCHAR(20)
DECLARE @on_fail_step_id            NVARCHAR(20)
DECLARE @retry_attempts                NVARCHAR(20)
DECLARE @retry_interval                NVARCHAR(20)
DECLARE @os_run_priority            NVARCHAR(20)
DECLARE @subsystem                    NVARCHAR(30)
DECLARE @command                    NVARCHAR(2000)
DECLARE @database_name                NVARCHAR(30)
DECLARE @flags                        NVARCHAR(20)
DECLARE @sch_name                    NVARCHAR(30)
DECLARE @sch_enabled                NVARCHAR(20)
DECLARE @freq_type                    NVARCHAR(20)
DECLARE @freq_interval                NVARCHAR(20)
DECLARE @freq_subday_type            NVARCHAR(20)
DECLARE @freq_subday_interval        NVARCHAR(20)
DECLARE @freq_relative_interval        NVARCHAR(20)
DECLARE @freq_recurrence_factor        NVARCHAR(20)
DECLARE @active_start_date            NVARCHAR(20)
DECLARE @active_end_date            NVARCHAR(20)
DECLARE @active_start_time            NVARCHAR(20)
DECLARE @active_end_time            NVARCHAR(20)
DECLARE @schedule_uid                NVARCHAR(200)
 
DECLARE Type_cursor SCROLL CURSOR FOR
 
SELECT sj.name, sj.enabled, sj.notify_level_eventlog, sj.notify_level_email, sj.notify_level_netsend, sj.notify_level_page,
    sj.delete_level, sj.description, sc.name,sl.name,
    CASE sc.category_class WHEN 1 THEN N'JOB'
                            WHEN 2 THEN N'ALERT'
                            WHEN 3 THEN N'OPERATOR' END AS category_class,
    CASE sc.category_type WHEN 1 THEN N'LOCAL'
                            WHEN 2 THEN N'MULTI-SERVER'
                            WHEN 3 THEN N'NONE' END AS category_type
FROM dbo.sysjobs sj
    INNER JOIN sys.syslogins sl ON sj.owner_sid = sl.sid
    INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
    INNER JOIN msdb.dbo.syscategories sc ON sj.category_id = sc.category_id
WHERE sjs.step_id =1
/*조건에 Job이름을 넣으면 해당Job만 추출*/
--AND sj.name IN ('')
ORDER BY sj.name ASC
FOR READ ONLY
 
OPEN Type_cursor
FETCH FIRST FROM Type_cursor
    INTO @name, @enabled, @notify_level_eventlog, @notify_level_email, @notify_level_netsend, @notify_level_page,
        @delete_level, @description, @sc_name, @owner_sid, @category_class, @category_type
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
        PRINT '/***** JOB '  + @name + ' Script Export *****/'
        PRINT 'USE [msdb]
GO' + CHAR(10)
        PRINT 'BEGIN TRANSACTION'
        PRINT 'DECLARE @ReturnCode INT'
        PRINT 'SELECT @ReturnCode = 0' + CHAR(10)
        PRINT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''' + @sc_name + ''' AND category_class=1)'
        PRINT 'BEGIN'
        PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''' + @category_class + ''', @type=N''' + @category_type + ''', @name=N''' + @sc_name + ''''
        PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' + CHAR(10)
        PRINT 'END' + CHAR(10)
 
        PRINT 'DECLARE @jobId BINARY(16)'
        PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N''' + @name + ''','
        PRINT '        @enabled=' + @enabled + ','
        PRINT '        @notify_level_eventlog=' + @notify_level_eventlog + ','
        PRINT '        @notify_level_email=' + @notify_level_email + ','
        PRINT '        @notify_level_netsend=' + @notify_level_netsend + ','
        PRINT '        @notify_level_page=' + @notify_level_page + ','
        PRINT '        @delete_level=' + @delete_level + ','
        PRINT '        @description=N''' + @description + ''','
        PRINT '        @category_name=N''' + @sc_name + ''','
        PRINT '        @owner_login_name=N''' + @owner_sid + ''', @job_id = @jobId OUTPUT'
        PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' + CHAR(10)
 
 
    DECLARE Type_cursor2 SCROLL CURSOR FOR
    SELECT
        sjs.step_name, sjs.step_id, sjs.cmdexec_success_code, sjs.on_success_action, sjs.on_success_step_id, sjs.on_fail_action,
        sjs.on_fail_step_id,
        sjs.retry_attempts, sjs.retry_interval, sjs.os_run_priority, sjs.subsystem,
        REPLACE (sjs.command, '''', '''''') AS command,
        sjs.database_name, sjs.flags
    FROM dbo.sysjobs sj
        LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
        LEFT OUTER JOIN msdb.dbo.syscategories sc ON sj.category_id = sc.category_id
    WHERE sj.name = @name
    ORDER BY sjs.step_id ASC
    FOR READ ONLY
 
    OPEN Type_cursor2
    FETCH FIRST FROM Type_cursor2
        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 =0)
    BEGIN
 
            PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''' + @step_name + ''','
            PRINT '        @step_id=' + @step_id + ','
            PRINT '        @cmdexec_success_code=' + @cmdexec_success_code + ','
            PRINT '        @on_success_action=' + @on_success_action + ','
            PRINT '        @on_success_step_id=' + @on_success_step_id + ','
            PRINT '        @on_fail_action=' + @on_fail_action + ','
            PRINT '        @on_fail_step_id=' + @on_fail_step_id + ','
            PRINT '        @retry_attempts=' + @retry_attempts + ','
            PRINT '        @retry_interval=' + @retry_interval + ','
            PRINT '        @os_run_priority=' + @os_run_priority + ', @subsystem=N''' + @subsystem +''','
            PRINT '        @command=N''' + @command + ''','
            IF (@database_name IS NOT NULL)
                BEGIN
                PRINT '        @database_name=N''' + @database_name + ''','
                END
            PRINT '        @flags=' + @flags
            PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' + CHAR(10) 
 
    FETCH NEXT FROM Type_cursor2
        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 Type_cursor2
    DEALLOCATE Type_cursor2

            PRINT 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1'
            PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

 
    DECLARE Type_cursor3 SCROLL CURSOR FOR
    SELECT
        sche.name, sche.enabled, sche.freq_type, sche.freq_interval, sche.freq_subday_type, sche.freq_subday_interval,
        sche.freq_relative_interval, sche.freq_recurrence_factor, sche.active_start_date, sche.active_end_date,
        sche.active_start_time, sche.active_end_time, sche.schedule_uid
    FROM dbo.sysjobs sj
        LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
        LEFT OUTER JOIN msdb.dbo.syscategories sc ON sj.category_id = sc.category_id
        LEFT OUTER JOIN msdb.dbo.sysjobschedules sjsche ON sj.job_id = sjsche.job_id
        LEFT OUTER JOIN msdb.dbo.sysschedules sche ON sjsche.schedule_id = sche.schedule_id
    WHERE sj.name = @name AND sjs.step_name = @step_name
    ORDER BY sche.name ASC
    FOR READ ONLY
 
    OPEN Type_cursor3
    FETCH FIRST FROM Type_cursor3
        INTO @sch_name, @sch_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, @schedule_uid
 
    WHILE (@@FETCH_STATUS =0)
    BEGIN
        IF (@sch_name IS NOT NULL)
        BEGIN
 
            PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''' + @sch_name + ''','
            PRINT '        @enabled=' + @sch_enabled + ','
            PRINT '        @freq_type=' + @freq_type + ','
            PRINT '        @freq_interval=' + @freq_interval + ','
            PRINT '        @freq_subday_type=' + @freq_subday_type + ','
            PRINT '        @freq_subday_interval=' + @freq_subday_interval + ','
            PRINT '        @freq_relative_interval=' + @freq_relative_interval + ','
            PRINT '        @freq_recurrence_factor=' + @freq_recurrence_factor + ','
            PRINT '        @active_start_date=' + @active_start_date + ','
            PRINT '        @active_end_date=' + @active_end_date + ','
            PRINT '        @active_start_time=' + @active_start_time + ','
            PRINT '        @active_end_time=' + @active_end_time + ','
            PRINT '        @schedule_uid=N''' + @schedule_uid + ''''
            PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
        END
 
    FETCH NEXT FROM Type_cursor3
        INTO @sch_name, @sch_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, @schedule_uid
    END
    CLOSE Type_cursor3
    DEALLOCATE Type_cursor3
 
 
 
        PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)'''
        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:' +CHAR(10)
        PRINT 'GO' + CHAR(10)
 
FETCH NEXT FROM Type_cursor
    INTO @name, @enabled, @notify_level_eventlog, @notify_level_email, @notify_level_netsend, @notify_level_page,
        @delete_level, @description, @sc_name, @owner_sid, @category_class, @category_type
END
 
CLOSE Type_cursor
DEALLOCATE Type_cursor

<출처 : 본인작성>

 

이상