Редагувати

Поділитися через

Create a SQL Server Agent Job in SQL Server Management Studio (SSMS)

  • Стаття

Applies to: SQL Server Azure SQL Managed Instance

Important

On Azure SQL Managed Instance, most SQL Server Agent features are supported. See Azure SQL Managed Instance T-SQL differences for more details.

This article explains how to create a SQL Server Agent job using SQL Server Management Studio (SSMS), Transact-SQL (T-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.

Prerequisites

  • User must be a member of SQL Server Agent fixed database roles or the sysadmin role.
  • Only job owners or members of sysadmin can modify jobs.
  • Assigning a job to another login does not guarantee sufficient permissions to run the job.

Security Considerations

  • Only sysadmin can change the job owner.
  • Sysadmin can assign job ownership to other users and run any job.
  • Jobs with steps requiring proxy accounts need to ensure the new owner has access to those proxies, or the job will fail.

For detailed security information, see Implement SQL Server Agent Security

How to Create a Job using SSMS

  1. In Object Explorer, expand the server where the job will be created.
  2. Expand SQL Server Agent.
  3. Right-click Jobs and select New Job....
  4. On the General page, configure job properties. For more details, see Job Properties - General Page.
  5. On the Steps page, configure the job steps. For more details, see Job Properties - Steps Page.
  6. On the Schedules page, set job schedules. For more details, see Job Properties - Schedules Page.
  7. On the Alerts page, configure job alerts. For more details, see Job Properties - Alerts Page.
  8. On the Notifications page, configure job completion notifications. For more details, see Job Properties - Notifications Page.
  9. On the Targets page, configure the target servers. For more details, see Job Properties - Targets Page.
  10. Select OK to save the job.

How to Create a Job Using Transact-SQL (T-SQL)

  1. In Object Explorer, connect to the server.

  2. Open a New Query window.

  3. Copy and paste the following script:

    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 ;
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 details, see:

Use SQL Server Management Objects

To create a SQL Server Agent job using SQL Server Management Objects (SMO):

Call the Create method of the Job class by using a programming language that you choose, such as Visual Basic, Visual C#, or PowerShell. For example code, see Scheduling Automatic Administrative Tasks in SQL Server Agent.

Related content