Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (1)   |   Related Tips: More > 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.

connect to Ubuntu 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.

SQL Server Version

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.

db hosted on this server

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

create sql server job

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.

create sql server job

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

add job step

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.

create a scheudle

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

attach job to schedule

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

Assign job to sql server

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

execute the job

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. 

job history

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.

job history
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.


Last Update:






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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, July 04, 2017 - 7:56:56 AM - LucLemaire Back To Top

Hi,

How did you connect with putty to the to the Linux/Ubuntu server?

I can connect with SSMS and the server responds to ping.

Thank you.

 


Learn more about SQL Server tools