How to create and use SQL Server Agent Jobs: step by step

Automating recurring tasks in SQL Server saves time and reduces manual errors. This guide shows how to create and configure a SQL Server Agent Job to run scheduled T-SQL commands, useful for backups, maintenance, or simple ETL.

Prerequisites

• SQL Server instance with SQL Server Agent installed and running
• Permissions: be a member of SQLAgentUserRole in MSDB or sysadmin
• SQL Server Management Studio (SSMS) or access via T-SQL

Step 1: Understand what a SQL Server Agent Job is

A SQL Server Agent Job is composed of steps, each step executes a type of task (T-SQL, PowerShell, CmdExec). It also includes schedules, notifications and security properties. Knowing this prevents creating incomplete jobs or jobs without error handling.

Step 2: Create a simple Job via SSMS

Using SSMS is the most direct visual way: create the job, add steps and define the schedule.

-- Exemplo T-SQL equivalente para criar um job via script (ver passo 3 para detalhes)

Step 3: Create a Job via T-SQL (practical example)

This example creates a job that runs a simple T-SQL command to insert a record into an audit table. Copy and adapt to your needs.

USE msdb;
GO
-- 1. Criar o job
EXEC sp_add_job
    @job_name = N'InserirAuditoriaDiaria',
    @enabled = 1,
    @description = N'Insere um registo diário de auditoria.';

-- 2. Adicionar um passo que executa T-SQL
EXEC sp_add_jobstep
    @job_name = N'InserirAuditoriaDiaria',
    @step_name = N'InserirRegisto',
    @subsystem = N'TSQL',
    @command = N'
        IF OBJECT_ID(N''dbo.Auditoria'', N''U'') IS NULL
        BEGIN
            CREATE TABLE dbo.Auditoria (
                Id INT IDENTITY(1,1) PRIMARY KEY,
                Execucao DATETIME2 DEFAULT SYSUTCDATETIME(),
                Mensagem NVARCHAR(200)
            );
        END;
        INSERT INTO dbo.Auditoria (Mensagem) VALUES (N''Job executado'');
    ',
    @on_success_action = 1,
    @on_fail_action = 2;

-- 3. Criar um schedule diário às 02:30
EXEC sp_add_jobschedule
    @job_name = N'InserirAuditoriaDiaria',
    @name = N'ScheduleDiario0230',
    @freq_type = 4, -- diário
    @freq_interval = 1,
    @active_start_time = 23000; -- 02:30:00 -> 02:30 = 023000

-- 4. Associar o job ao servidor local
EXEC sp_add_jobserver
    @job_name = N'InserirAuditoriaDiaria',
    @server_name = N'(LOCAL)';
GO

Step 4: Configure notifications and error handlers

It’s important to know when a job fails. You can configure operators and email notifications (Database Mail) or log failures into tables.

-- Exemplo: registar falhas adicionando um passo on_fail_action ou usando sp_add_notification
-- Configurar o Database Mail e um operador via SSMS é recomendado para alertas por e-mail.
-- Aqui um método simples: criar um passo final para gravar falhas numa tabela de logs (como fallback).

Step 5: Test and schedule manual execution

Before trusting the schedule, run the job manually and verify behavior and permissions. You can run it via SSMS or T-SQL:

-- Executar job manualmente
USE msdb;
EXEC sp_start_job @job_name = N'InserirAuditoriaDiaria';

Verify the result

To confirm the job ran: check the job history in SSMS (Job Activity Monitor → View History) and query the dbo.Auditoria table in the target database.

-- Verificar entradas
SELECT TOP 10 * FROM dbo.Auditoria ORDER BY Execucao DESC;

-- Verificar historial do job
USE msdb;
SELECT sj.name, sh.run_date, sh.run_time, sh.message, sh.run_status
FROM dbo.sysjobhistory sh
JOIN dbo.sysjobs sj ON sh.job_id = sj.job_id
WHERE sj.name = N'InserirAuditoriaDiaria'
ORDER BY sh.instance_id DESC;

Conclusion

Automating with SQL Server Agent improves reliability and consistency of recurring tasks. Next steps: add PowerShell steps for ETL, configure notifications with Database Mail and manage permissions with proxies. Tip: always test jobs in a development environment before deploying them to production.