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?