Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

SQL Server Express Automated Backups

I recently set up a web application using SQL Server Express for a client, and it was the first time I had done any "production" level work using the Express edition. As such, this was the first time I ever needed to configure automated backups for a SQL Express database.  However, there is no built-in way to schedule backups without using manual scripts or external applications.  I briefly toyed with writing something myself to get the job done, but of course we should not reinvent the wheel so I turned to Google to find out what's out there for options.

After much searching, here's the solution I went with:  Jasper Smith (no relation!) has written an extremely useful and powerful stored procedure called expressmaint that handles backups, generates reports, performs validation, removes old files, and has many other options and features.  It's very complete and very easy to use.  In addition to the stored procedure, he provides an expressmaint application that does the same thing but completely from the command line.  All you need to do is schedule the execution of either of these with Windows Task Scheduler and you are good to go.  He provides complete source code and has many examples.

I found lots of examples of scripts out there to perform backups, but none of them had features like retaining backups or reports for custom durations, and none were as easy to use and configure and as flexible as the expressmaint solutions. 

Thanks, Jasper, and well done!

Also, don't forget that Mladen right here a SQLTeam has a great post with lots of free SQL Server tools that you may find useful, and some of them may even provide scheduled backups for SQL Express.  (I haven't checked them all out yet)

Are there any other free products or simple solutions out there for SQL Express developers that you've used? Any free GUI solutions out there?

Legacy Comments


Anthony
2008-05-14
re: SQL Server Express Automated Backups
Thanks for the tip. Just what I was looking for!

Jeffrey Brock
2008-12-11
re: SQL Server Express Automated Backups
Great post, however I cannot make this thing work. Can you post an example of your script? Thanks.