Edit

Share via

Create a SQL Server Agent job

Applies to: SQL Server Azure SQL Managed Instance

Important

On Azure SQL Managed Instance, most SQL Server Agent features are supported. For more information, see Azure SQL Managed Instance T-SQL differences.

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 articles in the Related content section.

Prerequisites

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

Security considerations

  • Only the sysadmin role can change the job owner.
  • The sysadmin role 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 fails.

For detailed security information, see Implement SQL Server Agent security

Use SQL Server Management Studio

  1. In Object Explorer, expand the server where you're creating the job.

  2. Expand SQL Server Agent.

  3. Right-click Jobs and select New Job....

  4. On the General page, configure job properties. For more information, see General page.

  5. On the Steps page, configure the job steps. For more information, see Steps page.

  6. On the Schedules page, set job schedules. For more information, see Schedules page.

  7. On the Alerts page, configure job alerts. For more information, see Alerts page.

  8. On the Notifications page, configure job completion notifications. For more information, see Notifications page.

  9. On the Targets page, configure the target servers. For more information, see Targets page.

  10. Select OK to save the job.

Use Transact-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

EXECUTE dbo.sp_add_job @job_name = N'Weekly Sales Data Backup';
GO

EXECUTE 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

EXECUTE dbo.sp_add_schedule
    @schedule_name = N'RunOnce',
    @freq_type = 1,
    @active_start_time = 233000;
GO

EXECUTE sp_attach_schedule
    @job_name = N'Weekly Sales Data Backup',
    @schedule_name = N'RunOnce';
GO

EXECUTE dbo.sp_add_jobserver @job_name = N'Weekly Sales Data Backup';
GO

For more information, 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

  • Last updated on