Scheduling regular database backups with SQL Server 2005 Express is more challenging than with the other SQL Server 2005 editions because SQL Server Express lacks the SQL Server Agent job scheduling service that the other editions use. However, you can create regularly scheduled and automated backup jobs thanks to the combination of Windows' built-in Task Scheduler and SQL Server Express's command-line sqlcmd utility. First, you need a backup script similar to the following one.
BACKUP DATABASE MediaCollection
      TO MediaBackup
Next, save these backup commands in a file named myMediaBackup.sql. You can execute this file by using the sqlcmd utility. To add the backup task to Windows Task Scheduler, access the Control Panel. Click Scheduled Tasks, select the Add Scheduled Task icon to start the Scheduled Task Wizard, then click Next. Select Command Prompt from the Application list, then click Next. Enter a task name in the dialog box (use a descriptive name such as SQL Backup), select Daily, then click Next. Enter the time, select the occurrence and the start date for the backup, then click Next. In the dialog boxes, enter a user name and password for running the job, then click Next. Click the "Open advanced properties for this task when I click Finish" box, then click Finish. In the Run dialog box, enter the following command, where the –i switch identifies the file that the sqlcmd utility will run:
sqlcmd –s (local)/SQLEXPRESS –i c:\sqlbackup\myMediaBackup.sql -E
This command creates a task scheduler job that will use the sqlcmd utility to run the backup commands specified in the MyMediaBackup.sql file at the time you choose. You can read about the T-SQL BACKUP command in the article, "Backing Up Your Database" in the archive at
http://www.sqlmag.com/Articles/ArticleID/93004/93004.html .
