How to backup a database in SQL Express 2005 or 2008

SQL Express is a great free database from Microsoft, it has many of the features of the full SQL Server application. Microsoft being Microsoft, however, have decided to make it a bit tricky for you to backup your databases -- you can manually do it from the Management Studio, but you can't do it from the Management section as there is no Maintenance Plans section. However it is pretty easy to acheive by writing a a very simple SQL script and then automating it using the Task Scheduler.

For this example we will backup the 'master' database, you will just need to amend any references to 'master' with the name of the database you want to backup.

Create the SQL script

First, create a text file called 'backup.sql' and open it in Notepad. Paste in the following text:

EXEC sp_addumpdevice 'disk', 'MediaBackup',
'D:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\BACKUP\master.bak'
BACKUP DATABASE master
TO MediaBackup

The above script just creates a 'disk media' called 'MediaBackup' and backs up the 'master' database into it.

Create the script to call the SQL script

Next we need to create a script (batch file) to run the SQL script we just created. Create a 'backup.cmd' file and edit it in Notepad.
Paste in the following text:

del "D:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\BACKUP\master.bak"
sqlcmd -S np:\\.\pipe\MSSQL$SQLEXPRESS\sql\query -i backup.sql

The first line above deletes the last backup file that you created. You can leave this out if you like, and the backup will be amended to the backup file -- obviously this file will grow large as multiple backups will be added into it.
The second line basically runs a SQL query using the contents of the file specified -- in this case the 'backup.sql' file.

Test

Once you have saved the both files, I would get to a command line and run the backup.cmd file.
Doing it from a command line ensures you can troubleshoot any errors which may be flagged up.

Schedule

Once you are happy that the backup is working, you just need to schedule the job by using Task Scheduler.