☰

编写不使用 SSMS 的 SQL 作业创建语句脚本

tag-icon • tag-icon sql-server scripting exec stored-procedures
编写不使用 SSMS 的 SQL 作业创建语句脚本

有人知道如何为 SQL 代理作业生成创建语句吗？我到处都找过了，似乎找不到不涉及使用 SSMS 和浏览对象资源管理器的搜索结果。我找到了 [msdb].[dbo].[sysjobsteps] 表，其中有作业的 UID 和步骤。我的想法是按 job_id 分组，按 step_id 排序，然后摸索一段时间，然后弄清楚如何连接。但我想在继续之前先在这里问一下。

答案1

Powershell 来救援：

import-module sqlps -disablenamechecking

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

答案2

这是一个开始，没有 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

相关内容