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:
for /f "tokens=2" %%d in ('echo %date%') do (
for /f "tokens=1-3 delims=/" %%j in ('echo %%d') do (
set /a day=%day%-1
if %day% lss 10 (
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.