Monday, October 19, 2009

AdventureWorks: just the MDF, please!!!

I find the K.I.S.S principle kind of offensive, but sometimes people ask for it. There's a bunch of guys developing a whole project on Codeplex to create a setup program for the AdventureWorks family of SQL Server sample databases. I wonder, is it so complicated to install a few databases? After all, people who will use it are very likely to be dba's or developers, and they certainly have some knowledge on SQL Server administration, or can understand some half-page instructions on how to restore a database backup or how to attach a database file.

Well, I needed the database. Downloaded the MSI for SQL Server 2008, and ran it. On setup things started to go south: setup asks for the instance on wich to install the databases, and presented me a list with the instances on my machine. I had only a default SQL Server Express instance. I selected it, but the "Next" button kept grayed, no matter what.

"Ok". Cancel setup. Go to Google and asks for "SQL Server Express AdventureWorks". There's a bunch of posts with some tortuous ways of doing it. One said, "run script instblablabla.sql installed with the MSI from Management Studio". Loaded the script - 190Kb (!). It creates the empty database structures and loads data from several CSV files that comes with the MSI too. Nope. Few errors, some restriction on having to enable FILESTREAM on SQL Server Express - unfortunatelly, my SQL Server Express installation is a x86 on top of Vista x64, and after some more Googling, I learned that this FILESTREAM stuff does not work on this scenario. But WHY do I need it??? Seems it is used to load data from the CSVs into the tables. [Sigh]. Think "complicated". Then double it.

Finally I lost my patience and downloaded the SQL Server *2005* version of AdventureWorks - from the Codeplex site itself. Extracted the MSI content and there they are, sitting beautifully on some subdir, the MDF and LDF files to the database. Attached them to my SQL Server Express that, inspite of being a 2008 installation, reads the 2005 MDF files without problems. Quick. Simple.

The MSI with the database files can be downloaded from http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004. Select the "AdventureWorksDB.msi" option.

To extract the MSI content, run the following command from a command prompt:
msiexec -a FileName.MSI -qb TARGETDIR=extract_location_full_path
To attach a MDF file to SQL Server Express, open Management Studio and, on Object Browser, right click on "Databases" and select "Attach..."

Is it really necessary a setup project for that???

3 comments:

  1. Usually for solve problems with sql server and files too I use other tools. But yesterday no one of theirs couldn't help me. All my sql files were damaged. But luckily for me I found in the Internet - repair database sql. It helped me for seconds and for free as I kept in mind.

    ReplyDelete
  2. Tks a lot. Quite simple!

    ReplyDelete
  3. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru Dot Net Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
    or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

    ReplyDelete