Over a million developers have joined DZone.

How to Backup a SQL Express Database

DZone's Guide to

How to Backup a SQL Express Database

· Database Zone ·
Free Resource

Download the Scale-Out and High Availability whitepaper. Learn why leading enterprises choose the Couchbase NoSQL database over MongoDB™ after evaluating side by side.

There are a lot of programs that ship with SQL Express these days. For example, when you install development tools like Visual Studio or SharePoint it is common practice to bundle it with an express version of SQL Server.

Most of the time, SQL Express is intended to be used for small development databases. However, once a SQL instance is made available to someone they will undoubtedly start using it for other reasons. The major problem with SQL Express is that it has no SQL Agent which is generally used to schedule maintenance jobs like a full backup. Before you know it, you have a mission critical application running off your SQL Express instance with no backups.

So how do we schedule a backup without using the SQL Agent? Well, we could shut down the database services and copy the mdf and ldf files but that means we have to take an outage. If the applications running on express are mission critical then this method will not go well with your end users. Therefore our only real choice is to run a hot backup by using a batch file which passes arguments to a command line sql tool. Here is the script that I came up with:

@echo off
SET SERVER=localhost

for /f "tokens=2" %%d in ('echo %date%') do (
for /f "tokens=1-3 delims=/" %%j in ('echo %%d') do (
set month=%%j
set day=%%k
set year=%%l
set /a day=%day%-1
if %day% lss 10 (
set day=0%day%
set year=%year:~2,3%
set mydate=%month%%day%%year%
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a%%b

sqlcmd -S %SERVER% -d master -Q "exec sp_msforeachdb 'BACKUP DATABASE [?] TO DISK=''%BACKUP_DIR%\?.Full.%mydate%.%XTIME%.bak'''

Note: Before you run this batch file you will probably want to change the BACKUP_DIR and the SERVER variables declared at the top of the file. In addition, if you are using SQL Authentication instead of windows you may want to specify the -U (login) and -P (password) switches for the sqlcmd. If you are running this on a SQL 2000 environment then you will probably need to use the osql command instead of sqlcmd. osql was replaced with sqlcmd starting with SQL 2005.

If everything goes right you should have a backup of all your databases. The files should include the database name followed by the date and time it was taken. This makes the files very easy to identify in the event that you have to perform a restore. Finally, I want to mention the fact that if you run this job on a regular frequency you will probably want to develop a routine which purges the old backups. Here is a script that I found on the Hey, Scripting Guy! Blog which will take care of that problem.


The Forrester Wave™: Big Data NoSQL report. See how the top NoSQL providers stack up. Download now.


Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}