How to automate database backups in SQL Server: step by step

João Barros 21 de August de 2026 5 min read

This tutorial shows how to automate database backups in SQL Server, including full and differential backups, and how to schedule tasks with SQL Server Agent. Automating backups is useful to protect data, meet recovery SLAs and reduce human error.

Prerequisites

SQL Server instance with SQL Server Agent active.
sysadmin permissions or permission to create jobs and execute BACKUP.
Folder on disk or share to store the .bak files.
SQL Server Management Studio (SSMS) recommended.

Step 1: Create a folder for backups and set permissions

It is important to have a dedicated folder for the .bak files and ensure the SQL Server service account has write permission. On Windows, create for example C:\SQLBackups and grant Full Control to the SQL Server service account.

Step 2: T-SQL script for full backup

Create and test a T-SQL script to perform a full database backup. This allows you to validate that the path and permissions are correct before scheduling.

BACKUP DATABASE [MinhaBaseDados]
TO DISK = N'C:\SQLBackups\MinhaBaseDados_FULL.bak'
WITH FORMAT, INIT, NAME = N'MinhaBaseDados-FULL',
     COMPRESSION, STATS = 10;

Explanation: WITH FORMAT and INIT overwrite the file; COMPRESSION reduces space (if supported); STATS shows progress.

Step 3: T-SQL script for differential backup

A differential backup is smaller and faster. Run it after a full backup and before scheduling at a higher cadence.

BACKUP DATABASE [MinhaBaseDados]
TO DISK = N'C:\SQLBackups\MinhaBaseDados_DIFF.bak'
WITH DIFFERENTIAL, INIT, NAME = N'MinhaBaseDados-DIFF', STATS = 10;

Common error: attempting a differential without a valid full backup beforehand — this will cause restore failure.

Step 4: Create a SQL Server Agent Job for full backup

Use SQL Server Agent to schedule the automated backup. In SSMS, expand SQL Server Agent > Jobs > New Job. Set a name and add a Step with the script from Step 2. Then create a Schedule (for example, weekly outside peak hours).

-- Exemplo de criação de job via T-SQL (simplificado)
USE msdb;
GO
EXEC dbo.sp_add_job @job_name = N'Backup_MinhaBaseDados_Full';
GO
EXEC sp_add_jobstep @job_name = N'Backup_MinhaBaseDados_Full',
    @step_name = N'FullBackup',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [MinhaBaseDados] TO DISK = N''C:\\SQLBackups\\MinhaBaseDados_FULL.bak'' WITH FORMAT, INIT, COMPRESSION, STATS = 10;';
GO
EXEC sp_add_jobschedule @job_name = N'Backup_MinhaBaseDados_Full',
    @name = N'WeeklyFull',
    @freq_type = 8, -- weekly
    @freq_interval = 1, -- every week
    @active_start_time = 230000; -- 23:00
GO
EXEC sp_add_jobserver @job_name = N'Backup_MinhaBaseDados_Full';
GO

Explanation: this example creates a job, adds a step with the T-SQL command and schedules it weekly at 23:00. Adjust freq_type/freq_interval/active_start_time as needed.

Step 5: Create a Job for regular differential backups

Create another job to run differential backups more frequently (for example, daily or hourly). Use the script from Step 3 as the Step.

-- Exemplo T-SQL para job diferencial (resumido)
USE msdb;
GO
EXEC dbo.sp_add_job @job_name = N'Backup_MinhaBaseDados_Diff';
GO
EXEC sp_add_jobstep @job_name = N'Backup_MinhaBaseDados_Diff',
    @step_name = N'DiffBackup',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [MinhaBaseDados] TO DISK = N''C:\\SQLBackups\\MinhaBaseDados_DIFF.bak'' WITH DIFFERENTIAL, INIT, STATS = 10;';
GO
EXEC sp_add_jobschedule @job_name = N'Backup_MinhaBaseDados_Diff',
    @name = N'DailyDiff',
    @freq_type = 4, -- daily
    @active_start_time = 020000; -- 02:00
GO
EXEC sp_add_jobserver @job_name = N'Backup_MinhaBaseDados_Diff';
GO

Step 6: Notifications and cleanup of old files

Add notifications to the job to send email on failure/success and implement a retention policy so the disk doesn't fill up (script to delete old .bak files).

-- Exemplo simples para apagar ficheiros com mais de 7 dias (PowerShell via job step)
$path = 'C:\SQLBackups'
Get-ChildItem -Path $path -Filter *.bak | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-7) } | Remove-Item -Verbose

In SQL Server Agent, create a job step of type PowerShell with this script. Common error: not testing the cleanup script manually before automating it.

Verify the result

Validate that the .bak files are generated in the folder at the scheduled times and that the jobs appear as successes in SQL Server Agent. To confirm integrity, restore a backup to a temporary database:

RESTORE DATABASE [MinhaBaseDados_Teste]
FROM DISK = N'C:\SQLBackups\MinhaBaseDados_FULL.bak'
WITH MOVE 'MinhaBaseDados_Data' TO 'C:\SQLData\MinhaBaseDados_Teste.mdf',
     MOVE 'MinhaBaseDados_Log' TO 'C:\SQLData\MinhaBaseDados_Teste_log.ldf',
     REPLACE, STATS = 10;

If the restore is successful, the backups are valid. Also test restoring combining FULL + DIFFERENTIAL, if applicable.

Conclusion

Automating backups in SQL Server with T-SQL scripts and SQL Server Agent reduces the risk of data loss and simplifies recovery. Next steps: add transaction log backups for lower RPOs, copy backups to a remote location or to Azure Blob Storage. Tip: always test the restore — a backup that does not restore is useless.