Michael Orlitzky { Backing up Microsoft SQL Server Express }

There's not too much background to this one. Microsoft offers a free version of its SQL Server product, called SQL Server Express. However,

A secret

There is an awesome utility called ExpressMaintenance which gives you access to most of these backup features; you just have to script it yourself.

Scriptin’ it

Most of the documentation was found in an old SQLdbatips article, but that site is now deceased. What's on Github will have to suffice now. For my purposes, I keep backups of all databases going back two days, with each database having its own backup directory. If this is acceptable to you, you can just use my sql_express_backup.bat batch file:

REM Nightly backup script utilizing the ExpressMaint.exe utility.
REM
REM -D ALL_USER -T DB -B %backup_dir%\%%x:
REM   Backup all user databases to backup_dir\instance_name.
REM
REM -BU DAYS, -BV 2:
REM   Keep backups for 2 days.
REM
REM -V: Verify the backup file.
REM -C: Continue on error.
REM
REM The carat character '^' allows you to split one command onto
REM multiple lines! Who knew?
REM


REM The path to the ExpressMaint.exe executable.
SET express_maint="C:\path\to\ExpressMaint.exe"

REM The names of the instances you'd like to back up.
SET instances=SQLEXPRESS SHAREPOINT

REM The directory in which you'd like to store the backups.
REM Subdirectories will be created for each instance.
SET backup_dir=C:\path\to\backups


for %%x in (%instances%) do (

%express_maint% ^
  -S %%x ^
  -D ALL ^
  -T DB ^
  -B "%backup_dir%\%%x" ^
  -BU DAYS ^
  -BV 2 ^
  -V ^
  -C

)

There are a few variables you'll want to modify near the top of the file, namely, express_maint, instances, and backup_dir.

You'll probably also want to schedule it to run every night, but that can be accomplished through the standard “Scheduled Tasks” thing.

michael orlitzky

Backing up Microsoft SQL Server Express

A secret

Scriptin’ it