Sunday, September 21, 2008

Backup SQLExpress 2008

SQL Express 2008 is a wonderful product. Perhaps my view is based on how bad MSDE was and how non-competititive the other "free versions" of SQL have been. But SQL Express 2008 is ready for prime time - although I still prefer the "free" version of DB2 - but the fact that it doesn't yet support LINQ means I'll use SQL 2008 until it does.


However, I found that when I install SQL Express, it also installs the SQL Agent but in disabled format. When I try to enable and use the agent, I'm told it's not supported in SQL Express. If it's not supported, why the heck install it MS? Oh well, I need to find another means to automate the backups of SQL Express. Enter SMO...


I first used SMO on a recent project at an ISV. We had used WIX to build our setups and the database stuff was the most difficult. We finally created a hybrid setup where part was using a standard MSI and part a simple .NET app that had the same look and feel for the SQL setup using the SMO objects. It worked great and made the setup application much simpler and nimble.


The SMO objects are super for setup, configuration, and management of SQL. Yes, I could've looked at the Powershell objects but I've worked with Powershell in the past and it is a bit too heavy for what I wanted to do- just backup my databases.


So the solution was to create a light console application in C# and run it from the Task Scheduler on the server. The code is available here and includes a precompiled version if you don't want to compile.


Or, the basic source code is here.

5 comments:

  1. Why bother with scripts when off-the-shelf freeware like SqlBackupAndFTP would do it better?

    ReplyDelete
  2. Great tutorial! Exactly what I needed :)

    ReplyDelete
  3. Thanks, this was insightful.

    @Ruslan: Sometimes you want to streamline and have full control. Or embed it. Then we bother..

    ReplyDelete
  4. Thank you for providing the code! Its saved me time going through SMO.

    ReplyDelete
  5. Thank you - just what I was looking for and worked brilliantly!

    ReplyDelete