Over a million developers have joined DZone.

How to Backup a SQL Express Database

· Database Zone

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

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 BACKUP_DIR=D:\Backups
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 Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:

Published at DZone with permission of Michael Ceranski, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}