SQL Server 2025 : SQL Server Agent2026/03/06

# 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                    2026-03-06 01:27:22.370
model                     2003-04-08 09:13:36.390
msdb                      2026-01-29 18:40:16.640
SampleDB                  2026-03-06 01:08:33.977
SampleDB2                 2026-03-06 01:09:00.190
TestDB_DailyJob           2026-03-06 01:28:09.787

(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 8776FE0E-61DC-4754-BE84-DBD2D97E8D0A             0             0

(1 rows affected)

1> select job_id,name from sysjobs; 
2> go 
job_id                               name                                
------------------------------------ ------------------------------------
8776FE0E-61DC-4754-BE84-DBD2D97E8D0A Daily Backup for TestDB             

(1 rows affected)