Scheduled backup using Microsoft SQL Server 2005

The Express version of MS SQL 2005 does not include functionality to perform a scheduled backup. To get around this, use the “Script to File” option and then run it from the command line using a batch file and Windows Scheduled Tasks function.

1. Open up SQL Server Management Studio Express
2. Drill down to your database then right-click and choose Backup
3. Specify all your backup options such as the name of the set and the destination file
4. Click OK and run the backup once to make sure it works.
5. Repeat steps 2 & 3 then click on the Script button at the top
6. Choose Script Action to File and specify a filename.
7. Save the file somewhere useful, you’ll need it in a moment.
8. Now create a command like the following:

sqlcmd -S .\SQL_INSTANCE -i D:\Path\To\Script\Daily-SQL-Backup.sql

Replace SQL_INSTANCE with the name of your instance. EG if it shows up in your services as SQL Server (MYDATABASE) then use MYDATABASE as the name.

Replace the filepath and name with the correct value for where you stored your .sql file back in step 6.

Note: The -S and -i flags are Case Sensitive.

9. Run the command in a command window to make sure it works. You should see a successful backup report.
10. If it works then create a batch file and put your command in it.
11. Now go to Start / Accessories / Scheduled Tasks and create task to run your batch file to your desired schedule.
12. TEST your backup by attaching the backed up data. Remember with backups, it’s ALL ABOUT THE RESTORE!

References:
http://www.sqldbatips.com/showarticle.asp?ID=27
http://www.brianmadden.com/blogs/guestbloggers/archive/2007/05/07/how-to-automate-the-backup-of-a-sql-server-2005-express-data-store.aspx