How can I auto-backup a SQL Server Express database?

Question

I've deployed MS Sql Server Express edition on a server with a database that's updated daily. It is important that I have regular backups of the database, preferably daily. But I can't hope to do this manually everyday. How can I have auto-backups of my databases on that server? A system that will preferably upload these databases to a FTP location.

Do you know of any tool that does that?

Or is there any script that I can write to create backups. I can then use a tool like Cobian Backup to automatically upload these backups to a remote location.

Thanks for your help.

Answer 1

The first answer is to use sql standard instead of express. However, given that it requires an outlay of cash you can look at the following article on sqldbatips.com

http://www.sqldbatips.com/showarticle.asp?ID=27

It's purpose is to show you how to create a simple tool for performing back ups. From there it's just a simple skip to uploading it to an ftp site.

EDIT: I just found this. http://sqlbackupandftp.com/ the domain name says it all.

Answer 2

I would take a look at a blog entry I wrote on the subject. It explains exactly how to backup SQL Server Express databases.

http://gatekiller.co.uk/Post/Automate_SQL_Server_Express_Backups

I would then, like you mentioned, use another tool to upload these backups to an FTP site.

If your thinking about moving to an open source database, I would suggest moving sooner rather than later. Integrating MySql with Microsoft technologies is easier than you think with the MySqlClient library.

http://dev.mysql.com/doc/refman/5.0/en/connector-net.html