SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server.

T-SQL includes a large collection of stored procedures that deal with SQL Server Agent jobs.

Here, we’ll use some of them to create and schedule a SQL Server Agent job.

You can also create SQL Server Agent jobs via the GUI (in SSMS) if you prefer.

Enable Agent XPs

If this is the first time you’re creating a SQL Server Agent job on your SQL Server instance, you’ll probably need to enable the SQL Server Agent extended stored procedures (Agent XPs).

Here’s how to do it using T-SQL, and here’s how to do it using the SSMS GUI.

Once you’ve done that, you can continue on and create a SQL Server Agent job like the one below.

Example

Here’s an example of the code used to create and schedule a SQL Server Agent job.

USE msdb;  
GO  
EXEC sp_add_job  
    @job_name = N'BackupPetHotelDB' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'BackupPetHotelDB',  
    @step_name = N'Run the backup',  
    @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE [PetHotel] TO DISK = N''C:\Backups\PetHotel.bak'' WITH CHECKSUM',
    @retry_attempts = 5,  
    @retry_interval = 5;  
GO
EXEC sp_add_schedule 
    @schedule_name = N'RunDailyAM',
    @freq_type = 4,
    @freq_interval = 4,
    @active_start_time = 041000;
GO  
EXEC sp_attach_schedule  
   @job_name = N'BackupPetHotelDB',  
   @schedule_name = N'RunDailyAM';  
GO  
EXEC sp_add_jobserver  
    @job_name = N'BackupPetHotelDB';  
GO

Here, we use five stored procedures in order to create and schedule the job. Most of these procedures accept a lot more (optional) arguments than what I’ve used in this example. For a full list of arguments available for each procedure, see the links in the table below.

These stored procedures are in the msdb database, and you can see that we set msdb as the current database before we ran them. Alternatively, you could qualify them with the database name (e.g. msdb.dbo.sp_add_job..., etc)

The following table provides a quick overview of each procedure.

Stored Procedure	Description
sp_add_job	Adds a new job executed by the SQL Agent service. See the Microsoft website for more information.
sp_add_jobstep	Adds a step that performs the actual operation for the job that was added with sp_add_job. In the above example, we add a step to backup a database. We specify that providing a T-SQL command. The @retry_interval is in minutes. See the Microsoft website for more information.
sp_add_schedule	Creates a schedule that can be used by any number of jobs. In this case, we gave @freq_type a value of 4 which is daily. We also gave @freq_interval a value of 4, which is also daily. The time is based on a 24-hour clock, and is entered in the form HHMMSS. Our example uses 041000, which is 04:10:00. See the Microsoft link below for the various options available when setting the frequency. See the Microsoft website for more information.
sp_attach_schedule	Sets a schedule for a job. Here, we attach the schedule that we added with sp_add_schedule to the job that we added with sp_add_job. Note that the schedule and the job must have the same owner. Also, a schedule can be set for more than one job, and a job can be run on more than one schedule. See the Microsoft website for more information.
sp_add_jobserver	Targets the specified job at the specified server. This also accepts a @server_name argument to specify the server. The default value is (LOCAL) for the local machine. In our example, we used the local machine and so we were able to omit this argument. See the Microsoft website for more information.

View the Job

You can use the sp_help_job stored procedure in the msdb database to view the SQL Server Agent jobs that have been created on the server.

EXEC sp_help_job;

Here’s what it looks like on my test machine:

In this case there are three jobs.

You can also see the jobs in the Object Explorer when you’ve got the SQL Server Agent > Jobs node expanded.

The sp_help_job stored procedure allows you to provide the name or ID of a job in order to get more detailed information about that job.

Example:

EXEC sp_help_job @job_name= 'BackupPetHotelDB';

Result:

The results are separated into the core job details, the job steps, the job schedules, and the job target servers. This pretty much reflects the stored procedures that we ran when we created the job.

See the Microsoft documentation for sp_help_job for more information, as well as a list of other arguments you can provide.

Test the Job

You can use the sp_start_job stored procedure to test that your job will run. This procedure accepts the job name or ID and it will run the job as soon as you execute the procedure.

See How to Run a SQL Server Agent Job using T-SQL for an example.