Create a JOB in SQL Server using SQL Query

SQL Server Agent allows the automation of administrative tasks such as backups, index maintenance, or running reports by defining SQL Server Jobs. While most people use SQL Server Management Studio (SSMS) for this, you can also create jobs programmatically using T-SQL.

This article will walk you through the process of creating a SQL Server Job using T-SQL.

Create a JOB in SQL Server using SQL Query

Basic Example of T-SQL Script

-- Step 1: Create the Job
EXEC msdb.dbo.sp_add_job
@job_name = N'SampleJob',
@enabled = 1,
@description = N'Job that selects the current date and time',
@owner_login_name = N'sa';  -- Change to appropriate login if needed
-- Step 2: Add a Job Step
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'SampleJob',
@step_name = N'SelectDateTime',
@subsystem = N'TSQL',
@command = N'SELECT GETDATE();',
@database_name = N'master',
@on_success_action = 1,  -- Quit with success
@on_fail_action = 2;     -- Quit with failure
-- Step 3: Create a Schedule
EXEC msdb.dbo.sp_add_schedule
@schedule_name = N'DailySchedule',
@freq_type = 4,              -- Daily
@freq_interval = 1,          -- Every day
@active_start_time = 090000; -- 9:00 AM (HHMMSS)
-- Step 4: Attach the Schedule to the Job
EXEC msdb.dbo.sp_attach_schedule
@job_name = N'SampleJob',
@schedule_name = N'DailySchedule';
-- Step 5: Add the Job to the Server
EXEC msdb.dbo.sp_add_jobserver
@job_name = N'SampleJob';

Notes on Parameters

Parameter	Description
@subsystem	Specifies the job type (e.g., TSQL, CmdExec, PowerShell)
@command	The actual script to run
@freq_type	1 = One-time, 4 = Daily, 8 = Weekly, etc.
@active_start_time	Time in 24h format (HHMMSS), e.g., 020000 = 2:00 AM

Steps to Create SQL Server Agent Job

Create the Job:

USE msdb;
GO
EXEC sp_add_job
@job_name = N'DailyDatabaseBackup',
@enabled = 1,
@description = N'Performs daily backup of the MyDatabase database.',
@owner_login_name = N'sa';

Add a Job Step:

EXEC sp_add_schedule
@schedule_name = N'Daily at Midnight',
@freq_type = 4,  -- Daily
@freq_interval = 1,
@active_start_time = 000000;  -- Midnight (hhmmss)

Add JOB Scheduler SQL Server:

EXEC sp_add_schedule
@schedule_name = N'Daily at Midnight',
@freq_type = 4,  -- Daily
@freq_interval = 1,
@active_start_time = 000000;  -- Midnight (hhmmss)

Attach Schedule to Job:

EXEC sp_attach_schedule
@job_name = N'DailyDatabaseBackup',
@schedule_name = N'Daily at Midnight';

Add Job to Server

EXEC sp_add_jobserver
@job_name = N'DailyDatabaseBackup';

Test SQL Server Job

To test the job without waiting for the schedule:

EXEC msdb.dbo.sp_start_job N'JobName';

Remove SQL Server Agent Job

EXEC sp_delete_job
@job_name = N'JobName';

Common Error and Fixasion

Error	Likely Cause	Fix
Login failed for user	Job owner doesn't have access	Change owner to sa or valid login
Invalid object name	Object/database doesn't exist or wrong DB context	Set correct database in step
Access denied	Agent account lacks file or network permissions	Grant rights to SQLAgent service account
Timeout expired	Query takes too long	Optimize SQL or increase timeout
Cannot open database	DB offline or inaccessible	Check database status
No process is on the other end of the pipe	Network/connection issues	Check network & SQL protocols

General Job Troubleshooting Checklist

1. Check Job History
   • Go to SQL Server Agent > Jobs
   • Right-click the job > View History
   • Look for red X icons (failures) and check the error messages.
2. Check Job Steps
   • Right-click the job > Properties > Steps
   • Review each step:
     • Is the command valid?
     • Is the database context correct?
     • Does it use the right SQL Server version/compatibility?
3. Check Job Schedule
   • Is the schedule set correctly?
   • Does the job actually run at the expected time?
   • Time zone mismatches or AM/PM errors?
4. Check Job Owner & Permissions
   • Is the job owned by a valid login?
   • Does the owner have sufficient permission on objects and databases?
   • Does the SQL Server Agent account have rights?
5. Check Logs / Output Files
   • In the job step, is "Output file" configured? (You can save command output to a .txt)
   • Are there any extra logs in SQL Server logs or Windows Event Viewer?
6. Check Linked Servers / Network Issues
   • If the job accesses remote resources (linked servers, UNC paths), are they online and accessible?
   • Are credentials valid?
7. Check Blocking or Resource Contention
   • Are there blocking queries when the job runs?
   • Is the job slow or timing out due to locks or long-running processes?
8. Check for Changes
   • Have any recent changes (schema, credentials, server settings) affected the job?

Summary

Creating SQL Server Agent jobs via SQL queries gives you full control and allows automation via scripts, deployments, or DevOps pipelines. It is especially helpful in enterprise environments or CI/CD systems where GUI access is limited.

Thanks