编写SQL作业创build没有SSMS的语句

Powershell来拯救：

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

这是一个开始，没有SSMS在编写脚本时添加的error handling。 它从MSDB中的相关表中获取用于运行sp_add_job和sp_add_jobstep

你也可以用@JobName参数创build一个存储过程，并把它传递给你想要编写的作业的名字。

 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