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 ExpressMaint which gives you access to most of these backup features; you just have to script it yourself.

Scriptin’ it

Most of the documentation you'd need can be found in this SQLdbatips article. 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