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...
Please read this blog post, I think it's useful: Schedule daily backup for sql server 2005 datbase using sql agent
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"
We have had good success with a product called Express Agent from Vale Software (http://www.valesoftware.com/products-express-agent.php).
You'll have to drop $79 to get it, but it works pretty well and I would say it is well worth it. It has a bunch of the functionality that you would get from the real SQL agent, but allows you to use the express edition of SQL server rather than moving up to Workgroup or beyond.
We've been using it for about a year and have it in a few hundred locations.
I have been using ExpressMaint for quite a while with great success.
We have used the combination of:
Cobian Backup for scheduling/maintenance
ExpressMaint for backup
Both of these are free. The process is to script ExpressMaint to take a backup as a Cobian "before Backup" event. I usually let this overwrite the previous backup file. Cobian then takes a zip/7zip out of this and archives these to the backup folder. In Cobian you can specify the number of full copies to keep, make multiple backup cycles etc.
ExpressMaint command syntax example:
expressmaint -S HOST\SQLEXPRESS -D ALL_USER -T DB -R logpath -RU WEEKS -RV 1 -B backuppath -BU HOURS -BV 3
sometime your hosting company doesn't allow for 3rd party software to be installed, so i came up with this method using service broker to schedule stuff in SQL Server Express:
it's completely sql server based and you don't need any 3rd party tools.
The folks at MSSQLTips have some very helpful articles, the one most relevant for this is "Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files"
The basic approach is to set up two tasks using the Windows Task Scheduler. One task runs a TSQL script that generates separate backup files for all MSSQL databases (except TEMPDB) with the database name and a date/time stamp in the file name into a specified directory. The second task runs a VBScript script that goes through that directory and deletes all files with a .BAK extension that are more than 3 days old.
Both scripts require minor editing for your environment (paths, how long to keep those database dumps) but are very close to drop-in-and-run.
Note that there are possible security implications if you're sloppy with these or with directory permissions, since they are plain text files that will need to run with some level of privilege. Don't be sloppy.
You can also try to use SQL Backup and FTP. This tool allows to backups your data even on SQL Server Express
I've stumbled accross this question just now when searching for the exact same issue. On another hit I've found detailed scripts you can schedule using the windows scheduler. Check it out here: http://www.sqldbatips.com/showarticle.asp?ID=27 Uses sqlcmd and some scripts you'll have to generate yourself (or just copy/paste). Saves the $80.