Automate the Backup of Your Microsoft SQL Server Express Databases
Provided by: John Litchfield, Database Analyst
Have you ever attempted to setup an automated backup of
your SQL Server Express database server, only to discover that the handy job
scheduler maintenance options that you are accustomed to using with the full
version of SQL Server are not available with the express edition? After a
brief search of Google, you may have noticed that others have managed to
implement this procedure, but they either ended up purchasing a 3rd
party product solution or they are using the less than ideal practice of
pausing the SQL Server Express Service in order to create a backup copy of
the MDF and LDF files (this means that the SQL Server is briefly taken
offline). This works, but for servers that must be live on a 24/7
basis, this is not the best solution.
Of course you can upgrade your SQL Server Express to
the full edition of SQL Server; however, this is very expensive. If your
current implementation of SQL Server Express meets your needs with the
exception of this dilemma with obtaining a backup, you will be enthused to
discover that there is a way to accomplish this task using the
Express Edition (without pausing the SQL Server service and without the
purchase of a 3rd party utility). Simply follow these steps:
Install SQL Server Management Studio Express
Backups require installing the SQL Server Management Studio
Express. This is a free download that comes with recent
versions of SQL Server Express. If you are using SQL
Server Express 2008 R2 SP1 or earlier, you need to
download it separately. For more information on the
different versions and links for downloads, visit our
SQL Server Express: Version Comparison Matrix and Free
Create the Backup Job
- Run SQL Server Management Studio Express.
- In the tree view, expand Server Objects => New
- The Backup Device dialog opens
- For Device Name, type in a name for your new backup job.
- For Destination, select the path to store the backups. In most cases,
it is best to use an location on another device.
- Press OK.
- Right click on the new backup device that you just
created and select the option called "Backup Database".
- Select the backup type (most often this is
- On the left side, select Backup Options and set the
- Name: Create a name for the backup job.
- Description (optional).
- Press OK.
- SQL Server runs the backup job to test it. If successful, it
with the following message. Press OK.
- To verify the backup output yourself, check the
destination folder to ensure that the backup file exists. The backup of the database is in the form of a
Repeat these steps for each database you wish to
backup. The setup of the backup job is now complete.
Setup a batch command to run the job
A batch file is used to run the SQL Server backup job
that you just finished creating. An example of the syntax
that you can use is below. (remember to save this file with a "BAT" file
--- < Begin Code Batch Script > ---
sqlcmd -S MYSERVER\OFFICESERVERS -E -Q
"BACKUP DATABASE MASTER TO TESTBACKUP"
--- < End Code Batch Script > -----
..where "MYSERVER" is the name of the SQL Server physical machine.
..where "OFFICESERVERS" is the name of the SQL Server.
..where "TESTBACKUP" is the name of the backup job.
..where "MASTER" is the name of the database.
Schedule the Job
In order for this task to run automatically, it must
be assigned to a scheduler program. There are a
couple of options for this:
- The built in Microsoft Windows Scheduler and
assign the batch file created above to a scheduled
- FMS offers a program
Total Visual Agent, which includes
the ability to execute and run a specified batch command
on the schedule that best suits your needs and maintain
an audit log.