Automating DB Backup under SQL Server Express (Without SQL Agent)

SQL Server Express does not include SQL Server Agent. That means:

• No Jobs engine
• No Maintenance Plans (Management → Maintenance Plans does not exist)

The same applies to many shared SQL Server environments. If you need automated backups, you must implement your own scheduling solution.

The Solution

Create a batch file and schedule it using Windows Task Scheduler. The script:

• Accepts database name as parameter
• Accepts backup folder as parameter
• Generates timestamped filename
• Executes BACKUP DATABASE via sqlcmd

Batch File Script

@echo off

set databaseName=%1
echo %databaseName%

set backupFolder=%2
echo %backupFolder%

for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"
set "fullstamp=%YYYY%-%MM%-%DD%_%HH%-%Min%-%Sec%"
echo %fullstamp%

set OutDir=%backupFolder%\%databaseName%_%fullstamp%.bak
echo %OutDir%

sqlcmd -S <serverName\instanceName> -U <user> -P <pass> -Q "BACKUP DATABASE [%databaseName%] TO disk='%OutDir%'"

How to Use

Save the script as backup.bat and call it like this:

backup.bat MyDatabase D:\SqlBackups

This generates a backup file with timestamp:

MyDatabase_2026-02-22_17-45-01.bak

Important Notes

• Ensure sqlcmd is available in PATH.
• Prefer Windows Authentication (-E) instead of storing credentials in plain text when possible.
• Make sure the backup directory is writable by the SQL Server service account.
• Test restore periodically. A backup without restore validation is not a backup strategy.

References

Filename timestamp in Windows CMD batch script

Automate the Backup of Your Microsoft SQL Server Express Databases

The second reference did not work for me because step #4 was missing. However, it led me to the official BACKUP DATABASE documentation, which resulted in this script.