How To Automate SQL Server Express Database Backups



Last updated: November 6th, 2026

Updated October 2026: added a PowerShell version with safe retention, the exact Task Scheduler command, the permissions that trip most people up, restore testing, and notes for SQL Server 2025 Express.

Quick Answer

SQL Server Express doesn't include SQL Server Agent, so it can't schedule its own backups. The standard workaround is:

  1. Create Microsoft's sp_BackupDatabases stored procedure in master.
  2. Call it from a .bat or PowerShell script with sqlcmd.
  3. Run that script daily with Windows Task Scheduler, and delete backups older than N days.

The whole setup takes about 15 minutes and backs up every database on the instance, including ones you add later.

Flow: Task Scheduler runs a PowerShell script, which calls sp_BackupDatabases with sqlcmd, then prunes old files and copies them off the server

Why SQL Server Express Needs a Workaround

On Standard and Enterprise editions you would create a SQL Server Agent job or a maintenance plan. Express has neither. Express is still fine for many production sites (SQL Server 2025 Express, generally available since November 2025, raised the per-database size limit from 10 GB to 50 GB and still has no Agent), but backups are your job. Windows Task Scheduler plays the role of SQL Agent.

Step 1: Create the sp_BackupDatabases Procedure

Microsoft publishes a free script for exactly this. Open SQL Server Management Studio (or Azure Data Studio, or sqlcmd), connect to your Express instance, and run the SQL_Express_Backups.sql script from Microsoft's "Schedule and automate backups of SQL Server databases in SQL Server Express" article. It creates dbo.sp_BackupDatabases in the master database.

The procedure takes three parameters:

  • @backupLocation: the folder to write to. It must end with a backslash, because the file name is appended directly.
  • @backupType: F = full, D = differential, L = transaction log.
  • @databaseName (optional): leave it out to back up every online database (tempdb is skipped).

Each run writes timestamped files such as MyAppDb_FULL_10292026_020000.BAK (differentials end in _DIFF_...BAK, log backups in _LOG_...TRN), so runs never overwrite each other. The procedure never deletes anything, which is why the scripts below add a retention step.

Step 2: Create the Backup Script

Option A: Batch file

Save this as C:\scripts\SqlBackup.bat. Change the instance name if yours isn't the default .\SQLEXPRESS:

@echo off
REM C:\scripts\SqlBackup.bat - full backup of every online database except tempdb
REM The trailing backslash on the folder matters: the procedure appends the file name.
set "backupFolder=C:\SQLBackups\daily\"

if not exist "%backupFolder%" mkdir "%backupFolder%"

sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC master.dbo.sp_BackupDatabases @backupLocation = N'%backupFolder%', @backupType = 'F'"

REM Delete backups older than 14 days
forfiles /p "%backupFolder:~0,-1%" /m *.BAK /d -14 /c "cmd /c del @path" 2>nul

Option B: PowerShell (recommended)

The PowerShell version only deletes old backups after a successful run, writes a log, and returns a failure code that Task Scheduler can show:

# C:\scripts\SqlBackup.ps1 - full backups with retention and a log file
$instance      = ".\SQLEXPRESS"
$backupFolder  = "C:\SQLBackups\daily\"
$retentionDays = 14
$logFile       = "C:\SQLBackups\backup.log"

New-Item -ItemType Directory -Force -Path $backupFolder | Out-Null

$query = "EXEC master.dbo.sp_BackupDatabases @backupLocation = N'$backupFolder', @backupType = 'F'"
$output = & sqlcmd -S $instance -E -b -Q $query 2>&1
$exitCode = $LASTEXITCODE

"$(Get-Date -Format s) exit=$exitCode $output" | Add-Content -Path $logFile

if ($exitCode -ne 0) {
    # Non-zero exit makes Task Scheduler show the run as failed.
    exit $exitCode
}

# Only prune old files after a successful run, so a broken job never deletes your last good backups.
Get-ChildItem -Path $backupFolder -Filter *.BAK |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$retentionDays) } |
    Remove-Item -Force

Run the script once by hand and check that a .BAK file appears for every database.

Step 3: Get the Permissions Right

Two different accounts are involved, and this is where most setups fail:

  • The account that runs the task connects with -E (Windows authentication), so it needs a SQL Server login with backup rights. Don't assume SYSTEM will work: since SQL Server 2012, NT AUTHORITY\SYSTEM isn't a sysadmin by default.
  • The SQL Server service account (usually NT Service\MSSQL$SQLEXPRESS) is what actually writes the file, so it needs Modify permission on C:\SQLBackups. If you see "Operating system error 5 (Access is denied)", this is why.
-- Give the scheduled-task account permission to run backups.
-- Simplest: make it a sysadmin (it then can back up every database):
CREATE LOGIN [SERVER01\sqlbackup] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [SERVER01\sqlbackup];

-- Least privilege alternative: db_backupoperator in each database, e.g.
-- USE [MyAppDb]; CREATE USER [SERVER01\sqlbackup]; ALTER ROLE db_backupoperator ADD MEMBER [SERVER01\sqlbackup];

Step 4: Schedule It With Task Scheduler

You can click through taskschd.msc (Create Basic Task > Daily > Start a program), or create the task in one command from an elevated prompt:

REM Run the backup every day at 2:00 AM as a Windows account that has SQL Server rights
schtasks /Create /TN "SQL Express Backup" /SC DAILY /ST 02:00 /RL HIGHEST ^
  /RU "SERVER01\sqlbackup" /RP * ^
  /TR "powershell.exe -NoProfile -ExecutionPolicy Bypass -File C:\scripts\SqlBackup.ps1"

REM Run it once now to test
schtasks /Run /TN "SQL Express Backup"

In the task's properties, make sure Run whether user is logged on or not is selected. After the first run, the Last Run Result column should show 0x0.

Step 5: Test a Restore

A backup you have never restored is a hope, not a backup. At minimum, verify the file; better, restore it under a different name once in a while:

-- Back up one database (works on every edition, including Express)
BACKUP DATABASE [MyAppDb]
TO DISK = N'C:\SQLBackups\MyAppDb_FULL.bak'
WITH INIT, CHECKSUM, STATS = 10;

-- Check that the file is readable and the checksums are valid
RESTORE VERIFYONLY
FROM DISK = N'C:\SQLBackups\MyAppDb_FULL.bak'
WITH CHECKSUM;
-- Restore a backup as a NEW database to prove it works (doesn't touch the original).
-- 1) Find the logical file names inside the backup:
RESTORE FILELISTONLY FROM DISK = N'C:\SQLBackups\daily\MyAppDb_FULL_10292026_020000.BAK';

-- 2) Restore under a different name, moving the files:
RESTORE DATABASE [MyAppDb_RestoreTest]
FROM DISK = N'C:\SQLBackups\daily\MyAppDb_FULL_10292026_020000.BAK'
WITH MOVE N'MyAppDb'     TO N'C:\SQLData\MyAppDb_RestoreTest.mdf',
     MOVE N'MyAppDb_log' TO N'C:\SQLData\MyAppDb_RestoreTest_log.ldf',
     RECOVERY, STATS = 10;

How Many Backups Should You Keep?

  • Daily fulls, kept 7 to 14 days covers most small sites. The scripts above do this.
  • Weekly and monthly copies: create a second and third task that write to C:\SQLBackups\weekly\ and monthly\ with longer retention, so you can go back further than two weeks.
  • Differentials (D) several times a day if the data changes a lot. Note that they depend on the most recent full backup.
  • Log backups (L) only work for databases in the FULL recovery model. Most Express databases use SIMPLE, where a log backup fails; if you see that error, stick to full and differential backups.

Get Backups Off the Server

Backups on the same disk don't protect you from a failed drive, ransomware or a deleted VM. Add a second step that copies the folder elsewhere: another server with robocopy, or object storage (Azure Blob, Amazon S3, Backblaze B2 or Bunny Storage) with a command-line tool such as azcopy or rclone. Encrypt or protect anything that leaves the server, because a .bak file contains all of your data.

On Standard or Enterprise? Use SQL Server Agent Instead

If you landed here looking for how to automate SQL Server backups in general: on editions that include SQL Server Agent, you don't need Task Scheduler. Create a Maintenance Plan in SSMS (Management > Maintenance Plans > Maintenance Plan Wizard, with a Back Up Database task and a Maintenance Cleanup task), or an Agent job in T-SQL:

-- SQL Server Standard/Enterprise only (Express has no SQL Server Agent)
USE msdb;
EXEC dbo.sp_add_job        @job_name = N'Nightly full backup';
EXEC dbo.sp_add_jobstep    @job_name = N'Nightly full backup', @step_name = N'Backup MyAppDb',
     @subsystem = N'TSQL',
     @command = N'BACKUP DATABASE [MyAppDb] TO DISK = N''D:\SQLBackups\MyAppDb_FULL.bak'' WITH INIT, CHECKSUM, COMPRESSION;';
EXEC dbo.sp_add_schedule   @schedule_name = N'Daily 2 AM', @freq_type = 4, @freq_interval = 1,
     @active_start_time = 020000;
EXEC dbo.sp_attach_schedule @job_name = N'Nightly full backup', @schedule_name = N'Daily 2 AM';
EXEC dbo.sp_add_jobserver  @job_name = N'Nightly full backup';

That example overwrites one file each night (WITH INIT); for multiple days of history, Ola Hallengren's DatabaseBackup procedure (below) creates timestamped files and cleans up old ones in one step.

SQL Express Backup Tools: Alternatives to sp_BackupDatabases

  • dbatools: a free PowerShell module with hundreds of SQL Server commands. Backups and cleanup are two lines:
# Alternative: dbatools (free PowerShell module) - backs up and prunes in two commands
Install-Module dbatools -Scope CurrentUser
Backup-DbaDatabase -SqlInstance .\SQLEXPRESS -Path C:\SQLBackups\daily -Type Full -CreateFolder
Find-DbaBackup -Path C:\SQLBackups\daily -BackupFileExtension bak -RetentionPeriod 14d | Remove-Item
  • Ola Hallengren's Maintenance Solution: the most widely used free backup and index maintenance scripts. On Express, call dbo.DatabaseBackup from sqlcmd in the same Task Scheduler setup, and you also get integrity checks and index maintenance.
  • Commercial backup tools such as SQLBackupAndFTP wrap the same scheduling, retention and off-site upload in a GUI, if you would rather not maintain scripts.
  • SQL Server Management Studio: right-click a database > Tasks > Back Up. Fine for a one-off backup, but it can't schedule anything on Express.

Frequently Asked Questions

Does SQL Server Express have SQL Server Agent?

No. Express editions (including SQL Server 2022 and 2025 Express) don't include SQL Server Agent, so scheduled backups need Windows Task Scheduler or another external scheduler.

How do I back up a SQL Server Express database automatically?

Create Microsoft's sp_BackupDatabases procedure in master, call it from a batch or PowerShell script with sqlcmd, and schedule that script daily with Task Scheduler. Add a cleanup step to delete old backups.

Why do I get "Operating system error 5 (Access is denied)" when backing up?

The SQL Server service account, not your Windows user, writes the backup file. Grant NT Service\MSSQL$SQLEXPRESS (or whatever account the service runs as) Modify permission on the backup folder.

Can SQL Server Express use backup compression?

No, including SQL Server 2025 Express. WITH COMPRESSION requires Standard edition or higher (SQL Server 2025 editions). If disk space is tight, compress the files afterward or shorten retention.

Can I back up a SQL Express database automatically with a batch file?

Yes. Option A above is a batch file: it calls sp_BackupDatabases through sqlcmd and deletes old .BAK files with forfiles. Schedule it with Task Scheduler. Keep the -b switch on sqlcmd so a failed backup returns an error code.

Where are SQL Server Express backups stored by default?

If you don't give a path, SQL Server uses the instance's default backup folder, typically C:\Program Files\Microsoft SQL Server\MSSQLnn.SQLEXPRESS\MSSQL\Backup. Using an explicit folder like C:\SQLBackups makes cleanup and off-server copies easier.

Summary

Microsoft's sp_BackupDatabases procedure, a short script, and a Task Scheduler entry give SQL Server Express the scheduled backups it lacks. Get the two sets of permissions right, keep a sensible retention window, copy the files off the server, and test a restore now and then.

Related: keeping IIS sites alive with Task Scheduler and automating IIS log cleanup.


Comments

No Comments

CAPTCHA

Tagged: SQL


Join my email list!



ryan
About Me

With over 15 years in tech, I've excelled as a senior software engineer, specializing in ASP.NET, C#, SQL, Azure, and front-end technologies. I've led diverse projects across various sectors, from startups to global corporations, particularly during my decade in the San Francisco Bay Area.


Sign Up Hostinger Email