How to schedule automatic SQL Server Express backups

SQL Server Express includes the full database engine but not SQL Server Agent, so there are no maintenance plans and no Agent jobs. The standard workaround is a T-SQL backup script run by sqlcmd from Windows Task Scheduler. This guide builds that setup end to end, including cleanup and a failure log.

What you need

• sqlcmd, installed with SQL Server Management Studio or the SQL Server command line utilities. Run sqlcmd -? in a command prompt to check.
• The instance name. A default Express install is usually .\SQLEXPRESS. You can confirm it in SQL Server Configuration Manager.
• A backup folder on a different disk from the data files, for example D:\SQLBackups.
• A Windows account for the scheduled task that has backup rights in SQL Server (a member of sysadmin, or db_backupoperator in each database).

Step 1: create the folders and grant access

SQL Server writes the .bak file using its service account, not your login. If that account cannot write to the folder, the backup fails with error 3201, operating system error 5.

mkdir D:\SQLBackups\scripts
mkdir D:\SQLBackups\logs
icacls "D:\SQLBackups" /grant "NT Service\MSSQL$SQLEXPRESS":(OI)(CI)M

For a default (unnamed) instance the account is NT Service\MSSQLSERVER. If you are not sure, run SELECT servicename, service_account FROM sys.dm_server_services; in SSMS.

Step 2: write the backup script

This script takes a full backup of every online user database into a time-stamped file, with checksums so damaged pages are caught at backup time. Save it as D:\SQLBackups\scripts\backup_all.sql.

SET NOCOUNT ON;
DECLARE @dir   nvarchar(260) = N'D:\SQLBackups\';
DECLARE @stamp nvarchar(20)  = FORMAT(SYSDATETIME(), 'yyyyMMdd_HHmmss');
DECLARE @db sysname, @file nvarchar(400);

DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4            -- skip master, tempdb, model, msdb
      AND state_desc = 'ONLINE';

OPEN dbs;
FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @file = @dir + @db + N'_FULL_' + @stamp + N'.bak';
    PRINT 'Backing up ' + @db + ' to ' + @file;
    BACKUP DATABASE @db TO DISK = @file WITH CHECKSUM, INIT, STATS = 25;
    FETCH NEXT FROM dbs INTO @db;
END
CLOSE dbs;
DEALLOCATE dbs;

Want to back up master and msdb too? Change database_id > 4 to name <> 'tempdb'. It is worth doing: master holds logins, and you will want it after a server rebuild.

Step 3: wrap it in a batch file with logging

The -b switch makes sqlcmd return an error code when any backup fails, so the batch file can record the failure instead of silently carrying on. Save as D:\SQLBackups\scripts\run_backup.bat.

@echo off
set LOG=D:\SQLBackups\logs\backup.log
echo ==== %DATE% %TIME% starting >> "%LOG%"
sqlcmd -S .\SQLEXPRESS -E -b -i "D:\SQLBackups\scripts\backup_all.sql" >> "%LOG%" 2>&1
if errorlevel 1 (
    echo ==== %DATE% %TIME% BACKUP FAILED >> "%LOG%"
    exit /b 1
)
echo ==== %DATE% %TIME% backup OK >> "%LOG%"

rem Remove old backups only after a successful run
powershell -NoProfile -ExecutionPolicy Bypass -File "D:\SQLBackups\scripts\cleanup.ps1" >> "%LOG%" 2>&1

Run it once by hand and check the log and the folder before scheduling anything.

Step 4: add cleanup of old backups

Without cleanup the disk fills up and backups start failing weeks later. This keeps 14 days. Run it first with -WhatIf to see what it would delete, then remove -WhatIf.

$keepDays = 14
Get-ChildItem 'D:\SQLBackups' -Filter *.bak -File |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$keepDays) } |
    Remove-Item -WhatIf

Cleanup deliberately runs only when the backup succeeds. If backups keep failing, you keep your older files instead of deleting your last good copy.

Step 5: schedule it with Task Scheduler

Create a daily 2:00 AM task that runs whether or not anyone is logged in. /RP * prompts for the account password so it is not stored in your command history.

schtasks /Create /TN "SQL Express Full Backup" ^
  /TR "D:\SQLBackups\scripts\run_backup.bat" ^
  /SC DAILY /ST 02:00 ^
  /RU "YOURDOMAIN\sqlbackup" /RP * /RL HIGHEST

Then open Task Scheduler, right-click the task and choose Run to test it under the real account. Most "works by hand, fails on schedule" problems come from the task account lacking SQL Server rights.

Optional: weekly full plus daily differential

For larger databases, take a full backup on Sunday and a differential on other days. Copy backup_all.sql to backup_diff.sql, change _FULL_ to _DIFF_, and add DIFFERENTIAL:

BACKUP DATABASE @db TO DISK = @file WITH DIFFERENTIAL, CHECKSUM, INIT, STATS = 25;

Schedule the full task with /SC WEEKLY /D SUN and the differential task with /SC WEEKLY /D MON,TUE,WED,THU,FRI,SAT. The full vs differential guide explains the restore trade-off.

Step 6: verify the backups

Check a backup file is complete and its checksums are valid:

RESTORE VERIFYONLY
FROM DISK = N'D:\SQLBackups\Sales_FULL_20260927_020000.bak'
WITH CHECKSUM;

This proves the file is readable, not that the database restores cleanly. Once a month, do a real test restore under a different name. See how to verify a SQL Server backup.

Step 7: get a copy off the machine

Backups on the same server do not survive a dead disk, theft or ransomware. Add a second copy somewhere else: Google Drive, an FTP/FTPS server, or a NAS. The 3-2-1 rule is a good target.

Limits of the script approach

This setup works and costs nothing, but know what it does not do:

• Nobody is told when it fails. The log records the failure, but someone has to read it. Adding email means Database Mail (not in Express) or a PowerShell Send-MailMessage step with SMTP credentials.
• No retries. A brief network or disk hiccup at 2:00 AM means no backup that day.
• Off-site copying is a separate script with its own credentials and failure modes.
• Password changes break it. When the task account's password changes, the task stops running until someone updates it.