创建作业Create a Job

适用对象: 是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

重要

Azure SQL 数据库托管实例目前支持大多数但并非所有 SQL Server 代理功能。On Azure SQL Database Managed Instance, most, but not all SQL Server Agent features are currently supported. 有关详细信息,请参阅 Azure SQL 数据库托管实例与 SQL Server 之间的 T-SQL 差异See Azure SQL Database Managed Instance T-SQL differences from SQL Server for details.

本主题说明如何通过使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio 或 SQL Server 管理对象 (SMO) 在 Transact-SQLTransact-SQL 中创建 SQL Server 代理作业。This topic describes how to create a SQL Server Agent job in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or SQL Server Management Objects (SMO).

若要添加可以发送到操作员的作业步骤、计划、警报和通知,请参阅“请参阅”部分中的主题的链接。To add job steps, schedules, alerts, and notifications that can be sent to operators, see the links to topics in the See Also section.

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 若要创建作业,用户必须是某个 SQL ServerSQL Server 代理固定数据库角色或 sysadmin 固定服务器角色的成员。To create a job, a user must be a member of one of the SQL ServerSQL Server Agent fixed database roles or the sysadmin fixed server role. 作业只能由其所有者或 sysadmin 角色的成员进行编辑。A job can be edited only by its owner or members of the sysadmin role. 有关 SQL ServerSQL Server 代理的固定数据库角色的详细信息,请参阅 SQL Server 代理固定数据库角色For more information about the SQL ServerSQL Server Agent fixed database roles, see SQL Server Agent Fixed Database Roles.

  • 将作业指派给另一个登录名并不保证新所有者有足够的权限来成功运行该作业。Assigning a job to another login does not guarantee that the new owner has sufficient permission to run the job successfully.

  • 本地作业是由本地 SQL ServerSQL Server 代理进行缓存的。Local jobs are cached by the local SQL ServerSQL Server Agent. 因此,任何修改都会隐式强制 SQL ServerSQL Server 代理重新缓存该作业。Therefore, any modifications implicitly force SQL ServerSQL Server Agent to re-cache the job. 由于直到调用 SQL ServerSQL Server sp_add_jobserver 时, 代理才缓存作业,因此最后调用 sp_add_jobserver 将更为有效。Because SQL ServerSQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.

安全性Security

  • 您必须是系统管理员才可以更改作业的所有者。You must be a system administrator to change the owner of a job.

  • 为了安全起见,仅作业所有者或 sysadmin 角色的成员可以更改作业的定义。For security reasons, only the job owner or a member of the sysadmin role can change the definition of the job. 只有 sysadmin 固定服务器角色的成员才可以将作业所有权分配给其他用户,并且他们可以运行任何作业,而不管作业所有者是谁。Only members of the sysadmin fixed server role can assign job ownership to other users, and they can run any job, regardless of the job owner.

    备注

    如果将作业所有权重新指派到的用户不是 sysadmin 固定服务器角色的成员,而执行作业的步骤需要代理帐户(例如, SSISSSIS 包执行),则请确保该用户可以访问该代理帐户,否则作业将失败。If you change job ownership to a user who is not a member of the sysadmin fixed server role, and the job is executing job steps that require proxy accounts (for example, SSISSSIS package execution), make sure that the user has access to that proxy account or else the job will fail.

PermissionsPermissions

有关详细信息,请参阅实现 SQL Server 代理安全性For detailed information, see Implement SQL Server Agent Security.

使用 SQL Server Management StudioUsing SQL Server Management Studio

创建 SQL Server 代理作业To create a SQL Server Agent job

  1. “对象资源管理器” 中,单击加号以展开要创建 SQL Server 代理作业的服务器。In the Object Explorer, click the plus sign to expand the server where you want to create a SQL Server Agent job.

  2. 单击加号以展开 “SQL Server 代理”Click the plus sign to expand SQL Server Agent.

  3. 右键单击“作业”文件夹,然后选择“新建作业…” 。Right-click the Jobs folder and select New Job....

  4. “新建作业” 对话框的 “常规” 页上,修改作业的常规属性。In the New Job dialog box, on the General page, modify the general properties of the job. 有关此页上可用选项的详细信息,请参阅作业属性 - 新建作业(“常规”页)For more information on the available options on this page, see Job Properties - New Job (General Page)

  5. “步骤” 页上,组织作业步骤。On the Steps page, organize the job steps. 有关此页上可用选项的详细信息,请参阅作业属性 - 新建作业(“步骤”页)For more information on the available options on this page, see Job Properties - New Job (Steps Page)

  6. “计划” 页上,组织作业的计划。On the Schedules page, organize schedules for the job. 有关此页上可用选项的详细信息,请参阅作业属性 - 新建作业(“计划”页)For more information on the available options on this page, see Job Properties - New Job (Schedules Page)

  7. “警报” 页上,组织作业的警报。On the Alerts page, organize the alerts for the job. 有关此页上可用选项的详细信息,请参阅作业属性 - 新建作业(“警报”页)For more information on the available options on this page, see Job Properties - New Job (Alerts Page)

  8. “通知” 页上,设置在作业完成时 MicrosoftMicrosoft SQL ServerSQL Server 代理要执行的操作。On the Notifications page, set actions for MicrosoftMicrosoft SQL ServerSQL Server Agent to perform when the job completes. 有关此页上可用选项的详细信息,请参阅作业属性 - 新建作业(“通知”页)For more information on the available options on this page, see Job Properties - New Job (Notifications Page).

  9. “目标” 页上,管理作业的目标服务器。On the Targets page, manage the target servers for the job. 有关此页上可用选项的详细信息,请参阅作业属性 - 新建作业(“目标”页)For more information on the available options on this page, see Job Properties - New Job (Targets Page).

  10. 完成后,单击 “确定”When finished, click OK.

使用 Transact-SQLUsing Transact-SQL

创建 SQL Server 代理作业To create a SQL Server Agent job

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute.

    USE msdb ;  
    GO  
    EXEC dbo.sp_add_job  
        @job_name = N'Weekly Sales Data Backup' ;  
    GO  
    EXEC sp_add_jobstep  
        @job_name = N'Weekly Sales Data Backup',  
        @step_name = N'Set database to read only',  
        @subsystem = N'TSQL',  
        @command = N'ALTER DATABASE SALES SET READ_ONLY',   
        @retry_attempts = 5,  
        @retry_interval = 5 ;  
    GO  
    EXEC dbo.sp_add_schedule  
        @schedule_name = N'RunOnce',  
        @freq_type = 1,  
        @active_start_time = 233000 ;  
    USE msdb ;  
    GO  
    EXEC sp_attach_schedule  
       @job_name = N'Weekly Sales Data Backup',  
       @schedule_name = N'RunOnce';  
    GO  
    EXEC dbo.sp_add_jobserver  
        @job_name = N'Weekly Sales Data Backup';  
    GO  
    

有关详细信息,请参阅:For more information, see:

使用 SQL Server 管理对象Using SQL Server Management Objects

创建 SQL Server 代理作业To create a SQL Server Agent job

通过使用所选编程语言(如 Visual Basic、Visual C# 或 PowerShell)来调用 Job 类的 Create 方法。Call the Create method of the Job class by using a programming language that you choose, such as Visual Basic, Visual C#, or PowerShell. 有关示例代码,请参阅 在 SQL Server 代理中计划自动管理任务For example code, see Scheduling Automatic Administrative Tasks in SQL Server Agent.