Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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?

Print | posted on Thursday, December 06, 2007 3:10 PM | Filed Under [ Techniques Code Library - SQL ]

Feedback

Gravatar

# re: SQL Server Express Automated Backups

Thanks for the tip. Just what I was looking for!
5/14/2008 5:16 AM | Anthony
Gravatar

# re: SQL Server Express Automated Backups

Great post, however I cannot make this thing work. Can you post an example of your script? Thanks.
12/11/2008 2:21 PM | Jeffrey Brock
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET