Назад | Перейти на главную страницу

Написание сценария SQL Job Create Statement без SSMS

Кто-нибудь знает, как сгенерировать оператор Create для задания агента SQL? Я искал повсюду и, похоже, не могу найти результат поиска, который бы не использовал SSMS и не просматривал Object Explorer. Я пошел и нашел таблицу [msdb]. [Dbo]. [Sysjobsteps], где есть UID задания и шаги. Моя идея заключалась в том, чтобы сгруппировать по job_id, упорядочить по step_id, а затем немного повозиться, прежде чем выяснить, как объединить. Но я подумал, что спрошу здесь, прежде чем идти по этому пути.

Powershell спешит на помощь:

import-module sqlps -disablenamechecking

$s = new-object microsoft.sqlserver.management.smo.server '.'
$a = $s.jobserver
$a.Jobs['your_job'].script()

Вот начало без обработки ошибок, которую SSMS добавляет при написании сценария. Он извлекает информацию из соответствующих таблиц в MSDB, которые используются для запуска sp_add_job и sp_add_jobstep

Вы также можете создать из этого хранимую процедуру с параметром @JobName и передать его в имени задания, которое вы хотите выполнить по сценарию.

USE msdb

/* Replace this with the name of the job you want to script out */
DECLARE @JobName sysname = N'My Job'

/* sp_add_job parameters */
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @enabled BIT
DECLARE @notify_level_eventlog INT
DECLARE @notify_level_email INT
DECLARE @notify_level_netsend INT
DECLARE @notify_level_page INT
DECLARE @delete_level INT
DECLARE @description NVARCHAR(1024)
DECLARE @category_name sysname
DECLARE @owner_login_name sysname

/* sp_add_jobstep parameters */
DECLARE @step_id INT
DECLARE @step_name sysname
DECLARE @command NVARCHAR(MAX)
DECLARE @additional_parameters NVARCHAR(MAX)
DECLARE @cmdexec_success_code INT
DECLARE @on_success_action TINYINT
DECLARE @on_success_step_id INT
DECLARE @on_fail_action TINYINT
DECLARE @on_fail_step_id INT
DECLARE @retry_attempts INT
DECLARE @retry_interval INT
DECLARE @os_run_priority INT
DECLARE @subsystem NVARCHAR(80)
DECLARE @server sysname
DECLARE @database_name sysname
DECLARE @database_user_name sysname
DECLARE @flags INT
DECLARE @proxy_name sysname
DECLARE @output_file_name NVARCHAR(400)


DECLARE @SQL NVARCHAR(MAX)

SELECT
    @job_id = job_id,
    @enabled = [enabled],
    @notify_level_eventlog = notify_level_eventlog,
    @notify_level_email = notify_level_email,
    @notify_level_netsend = notify_level_netsend,
    @notify_level_page = notify_level_page,
    @delete_level = delete_level,
    @description = [description],
    @category_name = c.name,
    @owner_login_name = o.name
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
INNER JOIN msdb.dbo.syslogins o ON j.owner_sid = o.sid
WHERE j.name=@JobName

IF @job_id IS NULL
BEGIN
    PRINT 'Job not found.'
    RETURN
END

SET @SQL = N'USE msdb
DECLARE @jobId BINARY(16)
DECLARE @ReturnCode INT

EXEC @ReturnCode =  msdb.dbo.sp_add_job
    @job_name=N''' + @JobName + ''', 
    @enabled=' + CAST(@enabled AS NVARCHAR(1)) + ', 
    @notify_level_eventlog=' + CAST(@notify_level_eventlog AS NVARCHAR(5)) + ', 
    @notify_level_email=' + CAST(@notify_level_email AS NVARCHAR(5)) + ', 
    @notify_level_netsend=' + CAST(@notify_level_netsend AS NVARCHAR(5)) + ', 
    @notify_level_page=' + CAST(@notify_level_page AS NVARCHAR(5)) + ', 
    @delete_level=' + CAST(@delete_level AS NVARCHAR(5)) + ', 
    @description=N''' + @description + ''', 
    @category_name=N''' + @category_name + ''', 
    @owner_login_name=N''' + @owner_login_name + ''',
    @job_id = @jobId OUTPUT
'

DECLARE JOB_STEPS CURSOR FOR
SELECT
    step_id,
    step_name,
    command,
    additional_parameters,
    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,
    [server],
    database_name,
    flags,
    p.name,
    output_file_name
FROM msdb.dbo.sysjobsteps s
LEFT JOIN msdb.dbo.sysproxies p ON p.proxy_id = s.proxy_id
WHERE job_id=@job_id
ORDER BY step_id

OPEN JOB_STEPS
FETCH NEXT FROM JOB_STEPS
    INTO @step_id,
         @step_name,
         @command,
         @additional_parameters,
         @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,
         @server,
         @database_name,
         @flags,
         @proxy_name,
         @output_file_name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = @SQL + '
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
    @job_id=@jobId,
    @step_name=N''' + @step_name + ''', 
    @command=N''' + REPLACE(ISNULL(@command,''),'''','''''') + ''',
    @additional_parameters=N''' + ISNULL(@additional_parameters,'') + ''',
    @step_id=' + CAST(@step_id AS NVARCHAR(5)) + ', 
    @cmdexec_success_code=' + CAST(@cmdexec_success_code AS NVARCHAR(5)) + ', 
    @on_success_action=' + CAST(@on_success_action AS NVARCHAR(5)) + ', 
    @on_success_step_id=' + CAST(@on_success_step_id AS NVARCHAR(5)) + ', 
    @on_fail_action=' + CAST(@on_fail_action AS NVARCHAR(5)) + ', 
    @on_fail_step_id=' + CAST(@on_fail_step_id AS NVARCHAR(5)) + ', 
    @retry_attempts=' + CAST(@retry_attempts AS NVARCHAR(5)) + ', 
    @retry_interval=' + CAST(@retry_interval AS NVARCHAR(5)) + ', 
    @os_run_priority=' + CAST(@os_run_priority AS NVARCHAR(5)) + ',
    @subsystem=N''' + ISNULL(@subsystem,'') + ''', 
    @server=N''' + ISNULL(@server,'') + ''', 
    @database_name=N''' + ISNULL(@database_name,'') + ''', 
    @flags=' + CAST(@flags AS NVARCHAR(5)) + ',
    @proxy_name=''' + ISNULL(@proxy_name,'') + ''', 
    @output_file_name=N''' + ISNULL(@output_file_name,'') + '''
    '

    FETCH NEXT FROM JOB_STEPS
    INTO @step_id,
         @step_name,
         @command,
         @additional_parameters,
         @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,
         @server,
         @database_name,
         @flags,
         @proxy_name,
         @output_file_name
END

CLOSE JOB_STEPS
DEALLOCATE JOB_STEPS


PRINT @SQL