Can I add Maintenance Plan in SQL Server for Linux?

Question

I am using SQL Server 2017 for Linux, and the Maintenance Plans are missing from the Management menu.

Does anyone knows if the Maintenance Plans are available for SQL Server for Linux? I can't find anything related to this topic.

Answer 1

As you can read on Microsoft SQL Server documentation (here on the Known Issues section), maintenance plans are not supported on SQL Server Management Studio for SQL Server on Linux.

However, you can create your own SQL Server jobs to perform backups, index and statistics maintenance as shown below.

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

For more information, please read this announcement.

Answer 2

To complement answer of Alberto Morillo: you also have option to use MS SQL Server Agent within the docker (Linux based) version of MS SQL Server. Then you will not need to create schedule using the TSQL-code, instead you will be able to set it via GUI.

To enable MS SQL Server Agent I use following command in the moment of creation docker container:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_AGENT_ENABLED=True" -e "MSSQL_PID=standard" -e "MSSQL_COLLATION=YourCollation" -e "MSSQL_SA_PASSWORD=123" -p "10.0.1.39:1433:1433" --name mssql -d mcr.microsoft.com/mssql/server:2017-latest