[5] SQL Server – Server Agent

Włączymy teraz SQL Server Agent, w celu wykonywania zaplanowanych zadań.

[1] Włącz SQL Server Agent.

[root@vlsr01 ~]# /opt/mssql/bin/mssql-conf set sqlagent.enabled true
Locale pl_PL not supported. Using en_US.
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
[root@vlsr01 ~]# systemctl restart mssql-server.service

[2] Dodaj zadanie i zweryfikuj jego wykonanie.

[root@vlsr01 ~]# sqlcmd -S localhost -U SA
Password: #wpisz hasło

#stwórz testową bazę
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                                                                                                                           2022-01-31 12:58:12.530
model                                                                                                                            2003-04-08 09:13:36.390
msdb                                                                                                                             2022-01-12 22:54:24.927
SampleDB                                                                                                                         2022-01-29 18:13:58.667
TestDB_DailyJob                                                                                                                  2022-01-31 12:59:11.163
(6 rows affected)

#przełącz się na systemową bazę
1> use msdb;
2> go
Changed database context to 'msdb'.

#dodaj i nazwij zadanie
1> exec dbo.sp_add_job @job_name = N'Daily Backup for TestDB';
2> go

#dodaj zadanie wykonania kopii zapasowej do TestDB
1> exec sp_add_jobstep
2> @job_name = N'Daily Backup for TestDB', @step_name = N'Backup database', @subsystem
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

#dodaj czas wykonania (dziennie, o 23:50)
1> exec dbo.sp_add_schedule
2> Backup for TestDB', @freq_type = 4, @freq_interval = 1, @active_start_time = 235000;
3> go

#dołącz do harmonogramu zadań
1> exec sp_attach_schedule
2> @job_name = N'Daily Backup for TestDB', @schedule_name = N'Daily Backup for TestDB';
3> go

#dołącz harmonogram do lokalnego serwera
1> exec dbo.sp_add_jobserver
2> @job_name = N'Daily Backup for TestDB', @server_name = N'(LOCAL)';
3> go

#uruchom zadanie
1> exec dbo.sp_start_job N'Daily Backup for TestDB';
2> go
Job 'Daily Backup for TestDB' started successfully.

#pokaż listę zadań
1> select * from sysjobschedules;
2> go
schedule_id job_id                               next_run_date next_run_time
----------- ------------------------------------ ------------- -------------
          8 5C26C22C-E9D8-4DBF-93FC-0822149422B5             0             0
(1 rows affected)
1> select job_id,name from sysjobs;
2> go
job_id                               name                                              
------------------------------------ --------------------------------------------------------------------------------------------------------------------------------
5C26C22C-E9D8-4DBF-93FC-0822149422B5 Daily Backup for TestDB                           
(1 rows affected)

[3] Jest możliwe sprawdzenie i upewnienie się, że wszystko jest OK, z poziomu klienta SSMS w Windows.