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,

• The Express edition is missing both the SQL Agent and database maintenance plans which would typically be used to back up a database.
• Simply backing up the data files is not safe. You may wind up with a snapshot of the database in an inconsistent state.

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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.