SQL Server 2022 : SQL Server Agent2023/11/24 |
Enable SQL Server Agent to run scheduled jobs.
|
|
[1] | Enable SQL Server Agent. |
root@dlp:~# /opt/mssql/bin/mssql-conf set sqlagent.enabled true SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. root@dlp:~# systemctl restart mssql-server |
[2] | Add a test job and verify working. |
root@dlp:~# sqlcmd -S localhost -U SA Password: # create a test DB 1> create database TestDB_DailyJob; 2> go 1> select name,create_date from sys.databases; 2> go name create_date ----------------------- ----------------------- master 2003-04-08 09:13:36.390 tempdb 2023-11-24 10:15:22.510 model 2003-04-08 09:13:36.390 msdb 2023-10-30 16:52:55.553 SampleDB 2023-11-24 09:51:11.083 SampleDB2 2023-11-24 10:02:21.497 TestDB_DailyJob 2023-11-24 10:15:30.347 (7 rows affected) # switch to the System DB 1> use msdb; 2> go Changed database context to 'msdb'. # add a job name 1> exec dbo.sp_add_job @job_name = N'Daily Backup for TestDB'; 2> go # add a job which takes backup of test DB 1> exec sp_add_jobstep 2> @job_name = N'Daily Backup for TestDB', @step_name = N'Backup database', @subsystem = N'TSQL', 3> @command = N'backup database TestDB_DailyJob to disk = \ 4~ N''/var/opt/mssql/data/TestDB_DailyJob.bak'' with noformat, noinit, \ 5~ name = ''TestDB-full'', skip, norewind, nounload, stats = 10', 6> @retry_attempts = 5, @retry_interval = 5; 7> go # add daily schedule (run at 23:50) 1> exec dbo.sp_add_schedule 2> @schedule_name = N'Daily Backup for TestDB', @freq_type = 4, @freq_interval = 1, @active_start_time = 235000; 3> go # attach a job to the schedule 1> exec sp_attach_schedule 2> @job_name = N'Daily Backup for TestDB', @schedule_name = N'Daily Backup for TestDB'; 3> go # add the schedule to local server 1> exec dbo.sp_add_jobserver 2> @job_name = N'Daily Backup for TestDB', @server_name = N'(LOCAL)'; 3> go # start job 1> exec dbo.sp_start_job N'Daily Backup for TestDB'; 2> go Job 'Daily Backup for TestDB' started successfully. # show job list 1> select * from sysjobschedules; 2> go schedule_id job_id next_run_date next_run_time ----------- ------------------------------------ ------------- ------------- 8 B7AB036D-DA14-4218-B84A-90018AA84B4A 0 0 (1 rows affected) 1> select job_id,name from sysjobs; 2> go job_id name ------------------------------------ ---------------------------- B7AB036D-DA14-4218-B84A-90018AA84B4A Daily Backup for TestDB (1 rows affected) |
[3] | It's possible to configure jobs from SSMS on Windows Client, too. |
Sponsored Link |
|