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.

Comments

Post a Comment

Popular posts from this blog

Decimal number issue from server to client. Wow! Didn't expect this @_@

-
Image
Chapter 17   where a poor Decimal Number came from Server Side to JavaScript I worked on a page for days, adjusting the UI with CSS and HTML. Everything looked fine… until I started testing multi-lingual support. English — OK. Spanish — OK. Chinese — OK. Even right-to-left Hebrew (crooked and askew, but working). But Russian? Nope. In Russian the page stopped working completely: only static texts and images were visible. Nothing dynamic rendered. Of course it was a JavaScript error. Firefox DevTools showed this: That error led me to this JS code: I stared at those lines and saw nothing wrong — no missing variables, no typos, nothing obvious. I went to the original project code to find the exact non-rendered rows and noticed a server variable embedded into the client-side script: Which naturally led me to the server-side C# code responsible for generating that value: Nothing looked wrong there either. So I did what every developer does in ...
Read more

Shrinking node_modules for AWS Lambda (My First “npm shock” and a Practical Fix)

-
Image
I’m a newbie in Node.js and not really familiar with the whole npm ecosystem. I honestly didn’t expect that a single npm install would explode into ~10MB spread across ~30 different folders. After a few minutes of staring at it, I realized what’s actually inside those folders: not just runtime code. What shocked me Inside node_modules you get everything: README files source files tests examples docs And then the next level of madness: modules inside modules . Duplicated dependencies nested multiple levels deep (e.g. inherits , core-util-is , tedious …), even if they already exist elsewhere in the project tree. Here’s a typical example: I’m in node_modules → package bl , and there’s another node_modules folder inside it, plus test , README and other “junk”… all for a ~10KB JS file. The obvious assumption (that turned out wrong) Once I accepted the chaos, I assumed there must be a “build for production” command that would “compile” depende...
Read more