How to Backup a SQL Express Database
Join the DZone community and get the full member experience.Join For Free
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.
Published at DZone with permission of Michael Ceranski, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.