THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Damian Widera

SQL Server on Linux: Running jobs with SQL Server Agent

 

SQL Server Team has announced that in CTP 1.4 you can schedule tasks using SQL Server Agent! That was one of the pain things at least for me – I usually have lot of tasks in the SQL Server Agent in my environments. Seems to me that as of now I will be able to test also on Linux.

I have copied the code from the blog just to show how simple it is to use SQL Server Agent Smile Good thing is that the stored procedures used to configure, manage and run the jobs have the same names in Linux like they have in SQL Server running on Windows

Here is a simple example:

  • Create a job

CREATE DATABASE SampleDB ;

USE msdb ;

GO

EXEC dbo.sp_add_job

@job_name = N’Daily SampleDB Backup’ ;

GO

  • Add one or more job steps

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

  • Create a job schedule

EXEC dbo.sp_add_schedule

@schedule_name = N’Daily SampleDB’,

@freq_type = 4,

@freq_interval = 1,

@active_start_time = 233000 ;

USE msdb ;

GO

  • Attach the schedule and add the job server

EXEC sp_attach_schedule

@job_name = N’Daily SampleDB Backup’,

@schedule_name = N’Daily SampleDB’;

GO

EXEC dbo.sp_add_jobserver

@job_name = N’Daily SampleDB Backup’,

@server_name = N'(LOCAL)’;

GO

  • Start job

EXEC dbo.sp_start_job N’ Daily SampleDB Backup’ ;

GO

 

You can read the SQL Server blog post here: http://bit.ly/2mUwjl4

Cheers
Damian

Published Saturday, March 18, 2017 7:07 PM by Damian

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement