SQL Server 2008 Sample Databases

If you remember good ol’ NorthWind, you’ll know that having a sample SQL Server database can be great for sampling new technologies and performing RDBMS experiments and tests.  As much as detail-oriented presenters and trainers like to use NorthWind, it is an old sample from SQL Server 2000 that doesn’t have many of the new features in SQL Server 2008.

I set out to get the sample databases, and I was amazed at the complexity of it.  Not that it is in general a difficult task, but it could probably be made much more simple by Microsoft.  Since I figured out which hoops you need to jump through,  I thought I would summarize it for you here:

1) Read Up

If you want to have a successful installation of the sample databases, take 20-30 minutes to become familiar with the SQL Server Database Product Samples.  Contrary to some of the complaints people have posted in the comments, there is lots of good documentation that helps you understand the contents and process.image

Two important points you might miss are that you need to have full-text search installed and FILESTREAM enabled to ensure a problem free install of the samples.  This can be done after the fact if you did not specify these settings during the initial installation.

It should also be noted that attempting to change the FILESTREAM setting through the SQL Server Configuration Manager did not seem to work even after a restart of the engine.  Instead I had to run this SQL

exec sp_configure ‘filestream_access_level’, ’2′
RECONFIGURE

 

2) Download & Run the Installer

Once you’ve familiarized yourself with the process and read the pre-requisites, download the .msi installer file:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040

For my Vista machine running SQL Server 2008, I picked this file: SQL2008.AdventureWorks_OLTP_DB_v2008.x86.msi

 

imageOne interesting point is that there appears to be an option to have the installer restore the database for you.  Unfortunately, when I attempted to use this option to restore it in my local SQLExpress instance, it bombed out on me requiring me to run the installer again without the option selected.  The option is not selected by default which leads me to believe that Microsoft is aware this is a potential problem area.

3) Restore the DB Manually

This is the part where people with little or no DBA knowledge probably begin to curse.  If you are running the same platform as me and accept all of the defaults, you would think this should be as simple as running this script in a new query window (based on step 6 in the instructions):

RESTORE DATABASE AdventureWorks2008
FROM DISK ‘C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008.bak’

Unfortunately, this gave me a bunch of errors based on the way the RESTORE was attempting to use the default settings.

image

You can get around this by using the restore script (RestoreAdventureWorks2008.sql) in the same folder as the .bak file.  If you look at how the path is being used, you’ll notice that you have to substitute the path as follows in the top section of the script:

SET @source_path = ‘C:\Program Files\Microsoft SQL Server\100\’ 

 

Summary

So after a bit of plugging away, I was able to the the sample successfully restored.  Yea!

image

I truly empathize with Microsoft as streamlining this installation can be a daunting task.  There are an endless number of settings that have to be accounted for, security, versions, etc.  However, this installation process is much too difficult for the average developer in my view.

Now that I have some data to play around with, I expect to post some experiences about using the new 2008 features such as intellisense and the other goodies I expect to find in the newest version of this tool.  All things considered, SQL Server is still my database platform of preference and I appreciate everything Microsoft does to make this free version available to the public.

About these ads

10 Responses

  1. [...] Scott Bateman has posted a very good article on SQL Server 2008 Sample Databases. [...]

  2. Thanks! This was very helpful, I started reading the official documentation and then my ADD kicked in LOL

  3. Agree with your comments about excessive complexity.

    When trying to use the script I get the following:

    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device ‘c:\Program Files\Microsoft SQL Server\100Tools\Samples\AdventureWorks2008.bak’. Operating system error 3(The system cannot find the path specified.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    Msg 5011, Level 14, State 5, Line 1
    User does not have permission to alter database ‘AdventureWorks2008′, the database does not exist, or the database is not in a state that allows access checks.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.

    Any suggestions – I am running out of hair!

  4. Graham,
    Did you check to make sure that the backup dump file (AdventureWorks2008.bak) does indeed exist in the specified path? c:\Program Files\Microsoft SQL Server\100Tools\Samples\

    If you are running anything that is 64bit, you might have a slightly different path.

    And this is the error you are getting from RestoreAdventureWorks2008.sql?

  5. Hi Scott,

    Many thanks for this post – I was having real problems with installing the AW DB’s.

    I got the same errors as you reported here including setting up FILESTREAM in config manager – rebooting, checking it was enabled and I still got an error and had to run your sp_configure script – however may I suggest you don’t recommend cutting and pasting your text – I did and ended up with the “wrong” type of quote marks which wasted 10 mins – must wear my glasses!

    Also the TODO comment at the top of SQL scipts say “Set the value of @source_path below to the root directory of your SQL Server 2008 samples installation, i.e. C:\Program Files\Microsoft SQL Server\100\” but if you follow the logic and set it to include …100\tools\samples\ you end up with the ..tools\samples part repeated twice as this is added lower in the code – so I think it should say set path to … Microsoft SQL Server\100\ – This could be just down to my installation or me being dumb!

    I am running Vista Ultimate – SQL 2008 Enterprise 180 day Evaluation – UK region settings. I am NOT a DBA and new-ish to SQL 2008, ( I’m more business / data warehouse dimensional modelling )

    Thanks again – I agree the installion is a bit complex – took me more than several hours having installed SQL with defaults but looking forward to exploring all the new stuff.

  6. Great job! It works fine for me now. Thanks

  7. My problem is the adventureworks2008.bak file doesnt exist. The sample database installs correctly (I have the 32 bit version) and there is no .bak file. I did a search and it isnt on my computer. Has anyone ever encoutered this problem? Is there a way to download this file separately?

  8. I apologize for the inconvenience, but do you happen to know if this also applies to the MS SQL Server 2008 Express edition? I basically have the same problem as this guy: http://social.msdn.microsoft.com/Forums/en-US/sqlserversamples/thread/0acd6be6-accd-4d62-a97e-4826c272fed4
    e.g.: I can go as far as selecting an instance (always SQLEXPRESS (engine)), but the “next” button is greyed out, and when I go back and return it’s not grey but clicking on it stops the installation process due to some error.

    (He’s finally solved it by installing the Enterprise evaluation edition which includes the Analytical Services “module” that the Express edition doesn’t have. I wonder if that’s the issue?)

    I’ve installed the Express with Advanced Services 86 edition. Full Text Search is installed (confirmed with the Microsoft SQL Server 2008 Setup Discovery Report option). I have enabled FILESTREAM both using that SQL query statement and through the server configuration screen. SQL Server is running, SQL Full-text filter Daemon launcher is running, SQL Server reporting services is running and the SQL server browser is running. The only thing that I have that is not running is the SQL Server Agent. I’ve restarted all the servers and I’ve also rebooted my comp at least once.

    I downloaded SQL2008.AdventureWorks_All_Databases.x86.msi, which comes with a bunch of different databases. Even if I don’t choose to install any of them except “Adventureworks” I still can’t continue past that “Select instance” part. Since the Express edition doesn’t have Analytical Services, does that mean I can’t support the OLAP/OLTP functionalities? Does that mean for the Express (with Advanced Services) edition the Adventureworks database samples can’t be used? Not sure what I’m doing wrong :(

  9. It should be noted that Microsoft seems to have fixed some of the glitches in their sample database installation method. You can now find something that is pretty close to a streamlined process here:

    http://msftdbprodsamples.codeplex.com/

    NOTE: the link on the step-by-step instructions page was broken at the time of this post, so it isn’t 100% complete, but at least it is good enough that you should be able to get where you need to go from the CodePlex site alone. Good Luck!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: