How can I schedule a daily backup with SQL Server Express? [duplicate]

Question

I'm running a small web application with SQL server express (2005) as backend. I can create a backup with a SQL script, however, I'd like to schedule this on a daily basis. As extra option (should-have) I'd like to keep only the last X backups (for space-saving reasons obviously) Any pointers?

[edit] SQL server agent is unavailable in SQL server express...

Answer 1

You cannot use the SQL Server agent in SQL Server Express. The way I have done it before is to create a SQL Script, and then run it as a scheduled task each day, you could have multiple scheduled tasks to fit in with your backup schedule/retention. The command I use in the scheduled task is:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -i"c:\path\to\sqlbackupScript.sql"

Answer 2

Eduardo Molteni had a great answer:

• SQL Server Automated Backups

Using Windows Scheduled Tasks:

In the batch file

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S  (local)\SQLExpress -i D:\dbbackups\SQLExpressBackups.sql 

In SQLExpressBackups.sql

BACKUP DATABASE MyDataBase1 TO  DISK = N'D:\DBbackups\MyDataBase1.bak'  WITH NOFORMAT, INIT,  NAME = N'MyDataBase1 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10  BACKUP DATABASE MyDataBase2 TO  DISK = N'D:\DBbackups\MyDataBase2.bak'  WITH NOFORMAT, INIT,  NAME = N'MyDataBase2 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10  GO