Schedule SQL Server Agent Job on a Linux based instance using T-SQL

By: Manvendra Singh | Comments (1) | Related: > SQL Server on Linux

Problem

SQL Server Agent support is included with the release of SQL Server vNext CTP 1.4 to enable DBAs and users to schedule any task using a SQL Server Agent Job on Linux. In this tip, I will explain how to create and run SQL Server Agent Jobs using T-SQL commands on a Linux based SQL Server instance.

Solution

Microsoft has included the SQL Server Agent feature with its latest release of SQL Server vNext CTP 1.4. An important point is you need to first install SQL Server Agent to create and run jobs on a Linux based SQL Server. In a previous tip, I explained how to install SQL Server Agent on Ubuntu server. SQL Server Agent jobs are very useful in automation of any kind of repetitive work. You can get the full list of the latest features supported here: SQL Server on Linux Release Notes.

SQL Server Agent Job Creation using T-SQL

Before going ahead, you need to have SQL Server vNext running on an Ubuntu Server. Also make sure that you have installed SQL Server Agent on this box to create jobs.

Step 1: First, connect to your Linux Server either using PuTTY or directly login to Linux/Ubuntu server using a valid username and password. I used PuTTY to connect to this server.

You can see we have connected to our target machine where we are going to install SQL Server Agent.

Step 2: We should be on version SQL Server vNext CTP 1.4 to create and run any SQL Server Agent Jobs. Make sure that you have installed SQL Server Agent which requires your SQL Server instance to be on CTP 1.4. Validate it by checking the installed version as per the below image. If you are still running a lower version then you should first upgrade to SQL Server CTP 1.4 then install SQL Server Agent before going ahead with the next step.

Step 3: I will create a backup job for one of my databases "MSSQLTIPS" hosted on this server. Let's check all the databases running on this server by executing the below T-SQL commands.

Step 4: Let's start with creating a SQL Server Agent Job to run a daily full backup of the "MSSQLTIPS" database. We will be using SQL Server stored procedures to create all the job steps, schedules and remaining details. You can use either sqlcmd or SSMS to run these T-SQL scripts to create the backup job. The first step is to use sp_add_job to create a new job.

#Add a new job name to SQL Server Agent
USE msdb
GO
EXEC dbo.sp_add_job @job_name = N'MSSQLTPS Daily Full Backup T-SQL' ;  
GO

Once the above command executes successfully, a dummy job will be created under the "Jobs" folder of SQL Server Agent. You will not find any details about this job as of now, so we will add steps, schedules, etc. in the next couple of steps. You can see the job in the below screenshot using SSMS on a Windows machine.

Step 5: Now we will add a job step to the job "MSSQLTPS Daily Full Backup T-SQL" by executing the below command. We are going to use the sp_add_jobstep stored procedure to add the job step. You need to change @job_name, @step_name, @subsystem, and @command as per your need.

 -- Adds a step to the job  
EXEC sp_add_jobstep  
    @job_name = N'MSSQLTPS Daily Full Backup T-SQL',  
    @step_name = N'Run Backup for MSSQLTIPS',  
    @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE MSSQLTIPS TO DISK = N''/var/opt/mssql/data/mssqltips.bak'' WITH NOFORMAT, NOINIT, NAME = ''MSSQLTIPS-full'', SKIP, NOREWIND, NOUNLOAD, STATS = 10',   
    @retry_attempts = 5,  
    @retry_interval = 5 ;  
GO

Step 6: Once the above command executes successfully, the next step is to create a schedule for this job. We will be using sp_add_schedule to create a daily schedule for this job. Change the parameters like schedule name, frequency and start time as per your needs. Execute the below code to create a schedule for this backup job.

#create a job schedule
USE msdb
GO
EXEC dbo.sp_add_schedule  
   @schedule_name = N'Daily FullBackup',  
   @freq_type = 4,  
   @freq_interval = 1,
   @active_start_time = 233000 ;

Once you press f5 to execute above command, the job schedule will be created.

Step 7: Now we need to attach this job schedule to our backup job "MSSQLTPS Daily Full Backup T-SQL" using sp_attach_schedule.

-- Attach the newly created job schedule in last step to our backup job 
USE msdb
go 
EXEC sp_attach_schedule  
   @job_name = N'MSSQLTPS Daily Full Backup T-SQL',  
   @schedule_name = N'Daily FullBackup';  
GO

Step 8: The Final step is to assign this job to the target server. Run the below command to assign this job to local system where I am logged in using sp_add_jobserver.

USE msdb
GO
EXEC dbo.sp_add_jobserver  
   @job_name = N'MSSQLTPS Daily Full Backup T-SQL',  
   @server_name = N'(local)';  
GO

Running the SQL Agent Job

We have created a daily full backup for our database MSSQLTIPS on this Linux based SQL Server. The next step is to validate whether it is working. We will execute this job to test its health. You can execute the job through SSMS as well, but I am going to kick off using T-SQL because this tip is focusing on the creation of SQL Server Agent Jobs using T-SQL. Run the sp_start_job command to execute the job.

--Execute the job
EXEC dbo.sp_start_job N'MSSQLTPS Daily Full Backup T-SQL';
GGO

Validate the SQL Server Agent Job Ran

We have two options to validate the execution, one is to check the job history and another is to check that the backup file (i.e. MSSQLTIPS.bak) was created under /var/opt/mssql/data/ which we specified in Step 5.

Let's first check the job history. Right click on the job name "MSSQLTPS Daily Full Backup T-SQL" in SSMS and choose show history. You will get a window like the below image. You can also run sp_help_jobhistory to get the job history details.

We can also login to the Linux server and check the backup file in the correct location /var/opt/mssql/data/. Below we can see the backup file exists.

Next Steps

Go ahead and create SQL Server Agent Jobs as per your needs.
I will explain the step by step process of SQL Server Agent Job creation on a Linux server using the GUI in my next tip.
Explore more knowledge on SQL Server Database Administration Tips.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

How to schedule a SQL Server Agent Job on a Linux based instance using T-SQL