Create and run SQL Server Agent jobs on Linux

THIS TOPIC APPLIES TO: yesSQL Server (Linux only)noAzure SQL DatabasenoAzure SQL Data WarehousenoParallel Data Warehouse

SQL Server jobs are used to regularly perform the same sequence of commands in your SQL Server database. This tutorial provides an example of how to create a SQL Server Agent job on Linux using both Transact-SQL and SQL Server Management Studio (SSMS).

  • Install SQL Server Agent on Linux
  • Create a new job to perform daily database backups
  • Schedule and run the job
  • Perform the same steps in SSMS (optional)

For known issues with SQL Server Agent on Linux, see the Release Notes.

Prerequisites

The following prerequisites are required to complete this tutorial:

  • Linux machine with the following prerequisites:

The following prerequisites are optional:

Install SQL Server Agent

To use SQL Server Agent on Linux, you must first install the mssql-server-agent package on a machine that already has SQL Server 2017 installed.

  1. Install mssql-server-agent with the appropriate command for your Linux OS.

    Platform Installation command(s)
    RHEL sudo yum install mssql-server-agent
    SLES sudo zypper refresh
    sudo zypper update mssql-server-agent
    Ubuntu sudo apt-get update
    sudo apt-get install mssql-server-agent
  2. Restart SQL Server with the following command:

    sudo systemctl restart mssql-server
    

Create a sample database

Use the following steps to create a sample database named SampleDB. This database is used for the daily backup job.

  1. On your Linux machine, open a bash terminal session.

  2. Use sqlcmd to run a Transact-SQL CREATE DATABASE command.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -Q 'CREATE DATABASE SampleDB'
    
  3. Verify the database is created by listing the databases on your server.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -Q 'SELECT Name FROM sys.Databases'
    

Create a job with Transact-SQL

The following steps create a SQL Server Agent job on Linux with Transact-SQL commands. The job runs a daily backup of the sample database, SampleDB.

Tip

You can use any T-SQL client to run these commands. For example, on Linux you can use sqlcmd or Visual Studio Code. From a remote Windows Server, you can also run queries in SQL Server Management Studio (SSMS) or use the UI interface for job management, which is described in the next section.

  1. Use sp_add_job to create a job named Daily SampleDB Backup.

    -- Adds a new job executed by the SQLServerAgent service
    -- called 'Daily SampleDB Backup'
    USE msdb ;
    GO
    EXEC dbo.sp_add_job
       @job_name = N'Daily SampleDB Backup' ;
    GO
    
  2. Call sp_add_jobstep to create a job step that creates a backup of the SampleDB database.

    -- Adds a step (operation) to the job
    EXEC sp_add_jobstep
       @job_name = N'Daily SampleDB Backup',
       @step_name = N'Backup database',
       @subsystem = N'TSQL',
       @command = N'BACKUP DATABASE SampleDB TO DISK = \
          N''/var/opt/mssql/data/SampleDB.bak'' WITH NOFORMAT, NOINIT, \
          NAME = ''SampleDB-full'', SKIP, NOREWIND, NOUNLOAD, STATS = 10',
       @retry_attempts = 5,
       @retry_interval = 5 ;
    GO
    
  3. Then create a daily schedule for your job with sp_add_schedule.

    -- Creates a schedule called 'Daily'
    EXEC dbo.sp_add_schedule
       @schedule_name = N'Daily SampleDB',
       @freq_type = 4,
       @freq_interval = 1,
       @active_start_time = 233000 ;
    USE msdb ;
    GO
    
  4. Attach the job schedule to the job with sp_attach_schedule.

    -- Sets the 'Daily' schedule to the 'Daily SampleDB Backup' Job
    EXEC sp_attach_schedule
       @job_name = N'Daily SampleDB Backup',
       @schedule_name = N'Daily SampleDB';
    GO
    
  5. Use sp_add_jobserver to assign the job to a target server. In this example, the target is the local server.

    EXEC dbo.sp_add_jobserver
       @job_name = N'Daily SampleDB Backup',
       @server_name = N'(LOCAL)';
    GO
    
  6. Start the job with sp_start_job.

    EXEC dbo.sp_start_job N' Daily SampleDB Backup' ;
    GO
    

Create a job with SSMS

You can also create and manage jobs remotely using SQL Server Management Studio (SSMS) on Windows.

  1. Start SSMS on Windows and connect to your Linux SQL Server instance. For more information, see Manage SQL Server on Linux with SSMS.

  2. Verify that you have created a sample database named SampleDB.

    Create a SampleDB database

  3. Verify that SQL Agent was installed and configured correctly. Look for the plus sign next to SQL Server Agent in the Object Explorer. If SQL Server Agent is not enabled, try restarting the mssql-server service on Linux.

    Verify SQL Server Agent was installed

  4. Create a new job.

    Create a new job

  5. Give your job a name and create your job step.

    Create a job step

  6. Specify what subsystem you want to use and what the job step should do.

    Job subsystem

    Job step action

  7. Create a new job schedule.

    Job schedule

    Job schedule

  8. Start your job.

    Start the SQL Server Agent job

Next Steps

In this tutorial, you learned how to:

  • Install SQL Server Agent on Linux
  • Use Transact-SQL and system stored procedures to create jobs
  • Create a job that performs daily database backups
  • Use SSMS UI to create and manage jobs

Next, explore other capabilities for creating and managing jobs: