SQL Express 2008 is a wonderful product. Perhaps my view is based on how bad MSDE was and how non-competitive 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.