SQL Server 2005 Express Edition: A cautionary tale

by Arthur Fuller | Sep 19, 2005 5:41:00 PM

Tags: Programming languages, Databases, Arthur Fuller, Microsoft SQL Server 2005, SQL, SQL Express, SQL 2000, Microsoft SQL Server, SQL Server Newsletter

Takeaway: Arthur Fuller recounts his less-than-smooth installation of SQL Server 2005 Express Edition and offers a few suggestions based on what he learned along the way.

Like thousands of developers, I eagerly downloaded SQL Server 2005 Express Edition mere moments after receiving an e-mail from Microsoft. Unfortunately, after installing it on my development box, I realized that I had a mess on my hands. Hopefully, by sharing my experience with SQL Express, I may shed some light on what other developers might be going through with this installation. (Note: This article is not a panacea. I have yet to figure out the myriad of things that went wrong with my installation. I suggest that you take every possible care to recover from your installation.)

First, I'll outline the particulars of my setup:

• My dev box runs Windows 2003 Server, with all patches dutifully applied.
• The SQL installation thereupon has all the patches dutifully applied.
• My installation is Custom, since I do not like to place programs and data upon the same drive. I have several drives, but the case in point uses c:\ as the programs drive and f:\ as the data drive. (It's an old habit acquired from the days of DOS—programs on one drive, data on another.)

A rundown of the course of events

1. I install SQL Server 2005 Express Edition.
2. Everything fails: the old stuff (my rock-solid working installation of SQL 2000 with a dozen databases) and the new stuff.
3. I un-install SQL Express and reboot.
4. My attempt to run SQL 2000 fails.
5. I un-install SQL 2000 (along with Analysis Services and English Query) and reboot.
6. I re-install SQL 2000 on Windows Server 2003 and immediately get this message "does not support — you must install SP3."
7. I install SP3 and reboot.
8. SQL 2000 is running successfully! But to guarantee the safety of my existing databases, I choose a new directory as the base data directory.
9. I copy several MDF files from the previous directory to the new directory, and then I run "sp_attach_single_file_db" against each one in succession. (If you don't know what this means, open BOL and search for that procedure name.)
10. The newly installed instance of SQL 2000 recognizes the newly attached databases and I was back in business.
11. I install SQL Express on a box that passes the minimum hardware and software requirements but contains no previous SQL installation and nothing of consequence.
12. Success! Everything is once again intact, and SQL Express has her own sandbox in which to play, which risks nothing I cherish.

My suggestions for installing SQL Express

This was not fun. If you plan on installing SQL Express betas, do not install it on a box whose databases matter. And before installing it, I strongly advise you to back up everything concerning SQL Server 2000 that you consider even vaguely important. I also think it would be wise to do all of this on a weekend, since you may need the extra time to recover.

If you've installed SQL Express, I'd love to hear about your experience, so please relay your thoughts on the process in the article discussion.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Print/View all Posts Comments on this article

sql server 2005 express sheiba | 09/20/05

SQL or FUD? matthewmct@... | 02/28/06

SQL Server 2005 Express Edition problems msabot | 09/20/05

SQL Server 2005 Express Edition problems Fayero | 09/28/05

Duh! What is it about "beta" that you don't understand spiv@... | 09/20/05

Beta versions IndustrialController | 09/21/05

Anxiety vs Patience EnvisionOne | 09/26/05

In response to "Duh!......" meditrac@... | 09/28/05

Foul innocent_bystander | 10/03/05

The real message Spanr | 02/28/06