Backup SQL Server 2005 Express easily

By Jacques Bron on Saturday, April 05 2008

SQL server 2005 Express Edition is a great  (and free !) database tool from Microsoft. While the Express Edition doesn't have the full capabilities of SQL Server 2005 Standard or Enterprise editions, it is sufficient for running your average data-based website. Unfortunately, backing up an SQL database is not an easy task. You're never sure to backup everything that is needed in case of a database problem and the Express edition of SQL Server doesn't provide a utility like the Standard or Enterprise editions. Expressmaint is a free utility that comes in two flavours : a stored procedure that you can execute on your SQLServer Express and a stand-alone executable that you can call from a scheduled task on your PC or your web/database Server. This is the solution I'm currently using for my blog and my photoblog.

In order to seriously backup your database, you'll need to make sure that backups are made regularly and that their frequency is in accordance to the amount of data that is updated every day. You'll also need to backup the system databases (because you might need them to rebuild your database correctly if a crash occurs). Ideally, the backup tool you'll use should write a log of all the backup up activities and log possible errors. It should delete old backup files and logs automatically. Expressmaint does all this (and more). Here is what I have setup on my server :

• set up the recovery mode of my database to "full"
• created a scheduked task that performs a full backup of my database once a day
• created a scheduled task that does a backup of the database logs every hour
• created a scheduled task that does a system databases backup every three days.

Now evething is automated. I can sleep well. If you use this utility (or any other) make sure to test everything and verify that you are able to restore completely your database in the case of a system crash or a database corruption problem.

Useful Links :

• Expressmaint (part 1 and part 2)
• SQL Server backup scenarios
• SQL Server 2005 Backups