Create a Job
Applies to:
SQL Server
Azure SQL Managed Instance
Important
On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.
This article describes how to create a SQL Server Agent job in SQL Server by using SQL Server Management Studio, Transact-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:
To create a job, using:
Before You Begin
Limitations and Restrictions
To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role. A job can be edited only by its owner or members of the sysadmin role. For more information about the SQL 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.
Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL 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.
For security reasons, only the job owner or a member of the sysadmin role can change the definition of the job. 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.
Note
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, SSIS package execution), make sure that the user has access to that proxy account or else the job will fail.
Permissions
For detailed information, see Implement SQL Server Agent Security.
Use SQL Server Management Studio
To create a SQL Server Agent job
In the Object Explorer, select the plus sign to expand the server where you want to create a SQL Server Agent job.
Select the plus sign to expand SQL Server Agent.
Right-click the Jobs folder and select New Job....
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)
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)
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)
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)
On the Notifications page, set actions for Microsoft SQL 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).
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).
When finished, select OK.
Use Transact-SQL
To create a SQL Server Agent job
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
Copy and paste the following example into the query window and select 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:
Use SQL Server Management Objects
To create a SQL Server Agent job
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.