SQL Server 2019 : SQL Server Agent2020/01/17

# テスト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                 2020-01-17 16:39:54.560
model                  2003-04-08 09:13:36.390
msdb                   2019-12-06 16:24:38.153
SampleDB               2020-01-16 21:25:32.233
SampleDB2              2020-01-16 21:26:47.270
TestDB_DailyJob        2020-01-16 21:40:12.690

(7 rows affected)

# システム DB にスイッチ
1> use msdb; 
2> go 
Changed database context to 'msdb'.

# ジョブ名を登録
1> exec dbo.sp_add_job @job_name = N'Daily Backup for TestDB'; 
2> go 

# テストDBのバックアップを取得するジョブを登録
1> exec sp_add_jobstep 
2> @job_name = N'Daily Backup for TestDB', 
3> @step_name = N'Backup database', 
4> @subsystem = N'TSQL', 
5> @command = N'backup database TestDB_DailyJob to disk = \ 
6~ N''/var/opt/mssql/data/TestDB_DailyJob.bak'' with noformat, noinit, \ 
7~ name = ''TestDB-full'', skip, norewind, nounload, stats = 10', 
8> @retry_attempts = 5, 
9> @retry_interval = 5; 
10> go 

# 日次スケジュールを登録 (23:50実行)
1> exec dbo.sp_add_schedule 
2> @schedule_name = N'Daily Backup for TestDB', 
3> @freq_type = 4, 
4> @freq_interval = 1, 
5> @active_start_time = 235000; 
6> go 
Changed database context to 'msdb'.

# 登録したスケジュールとジョブと関連付ける
1> exec sp_attach_schedule 
2> @job_name = N'Daily Backup for TestDB', 
3> @schedule_name = N'Daily Backup for TestDB'; 
4> go 

# ローカルサーバーにスケジュールを割り当てる
1> exec dbo.sp_add_jobserver 
2> @job_name = N'Daily Backup for TestDB', 
3> @server_name = N'(LOCAL)'; 
4> go 

# ジョブを開始する
1> exec dbo.sp_start_job N'Daily Backup for TestDB'; 
2> go 
Job 'Daily Backup for TestDB' started successfully.

# 登録確認
1> select * from sysjobschedules; 
2> go 
schedule_id job_id                               next_run_date next_run_time
----------- ------------------------------------ ------------- -------------
          8 6F6926C1-DB08-463D-8EB8-4FA185EB011E             0             0

(1 rows affected)

1> select job_id,name from sysjobs; 
2> go 
job_id                               name                      
------------------------------------ --------------------------
6F6926C1-DB08-463D-8EB8-4FA185EB011E Daily Backup for TestDB   

(1 rows affected)