SQL SERVER – 2008 – Download and Install Samples Database AdventureWorks 2005 – Detail Tutorial

August 10, 2008 by pinaldave

Just a day ago I received question from reader who just installed SQL Server 2008. After installation user did not find any samples database along with installation. User want to install samples database which he is very much used to.

Sample database are now moved to Microsoft’s opensource site of Codeplex. Visit following link to visit the Sample Database page.

CodePlex SQL Server Sample Database

There are two different method to setup sample database. 1) Running SQL Script 2) Restoring Database Backup. There are few common steps in both the process. We will see the common process first and then after see both the method to setup sample database in detail.

Common Steps:

Download the .msi package from codeplex based on your operating system and your SQL Server version. I have used Adventureworks DB version of 2005 as that is what was question from user. If you download version of 2008 it can only be installed in version of 2008.

Most of the images are self explanatory so I will let images explain most of the details.

Above image displays the location where Sample Database is installed. Please note the location of same database as “C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks OLTP“

Method 1 : Using T-SQL Script

Open SQL Server Management Studio and open the file instawdb from the location where sample database are extracted “C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks OLTP“.

Now make sure that you find variable @data_path and change its values to the location of the database. This is very important task otherwise your database will be created with empty tables.

Once script is successfully ran, following resultset will be returned.

Check AdventureWorks database in object explorer.

Method 2 : Restoring Database Backup

This method requires to create empty database initially and then after restoring it from backup. Please pay attention to radio buttons and checkbox on the screen as it is very important to select right options when restoring database.

Location of Database backup is where sample database are extracted “C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks OLTP“.

Following screen is most important, make sure to select options displayed in the screen or database will not be restored.

Check AdventureWorks database in object explorer.

If you want to install AdventureWorks database version 2008, the methods works fine. There are additional sample database are available in the same package and they can be installed in similar way. If you want to install Northwind database follow my previous article SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases – Part 2.

Let me know what is your experience while setting up sample database with SQL Server 2008. Additionally, let me know what you think about this in depth explanation of process of setting up sample database.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Database, Pinal Dave, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Documentation, SQL Download, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 33 Comments

33 Responses

on August 11, 2008 at 12:56 am andar909

hi, andar here, i just read your post. i like very much. agree to you, sir.
on August 12, 2008 at 12:09 pm Kamran Shahid

Very nice
on August 25, 2008 at 12:52 pm mike

luckyregister is hosting my web site and i have a new web pages getting uploaded this week with forms to collect names emails squeeze pages they are for real estate so the luckyregister.com gives me choices of DATABASES: ACCESS, MYSQL, and SQLSERVER. BUT I DONT know how in the world it works or how to setup anything in the sql server. stuff. like i dont know how to tie the commands queries schemas to work. i dont eve n know what ot ask you thats how confused i am about this when i looked at the databse and what it has in its admin area.. is it easyy to setup but just dont have the directions what do i do if you were me.?????????

Mike
on September 1, 2008 at 2:02 am jeremy

This worked perfectly, even for a novice ;)
Thanks!
J
on September 4, 2008 at 12:42 am Lisa Clary

One thing to add to the docs is that filestreaming needs to be enabled. I’m an oracle person, so not everything is intuitive, and I felt like an idiot for not being able to get the samples installed. So your site was a wonderful resource and the only set of instructions that got me through this (I followed option 2) Thanks much! lc
on September 4, 2008 at 6:30 pm Jens

Thank you for this.
I was so annoyed at the SQL server samples install. It was so stupid. Sometimes I wish there would be a message saying :

“WARNING : Installing the sample databases look easy, but is actually quite difficult. Do not expect a simple wizard. Even though it looks like you are just going to press intall on the MSI package, you actually have to build the database from TSQL an change programming code to match your setting after this wizard has installed the files. Also, this wizard will not help you at all trying to figure out what kind of files you should open or at all how to make sense of this install. The documentation is able to help you, but it is structured in such a way that 90% is irrelevant and it is almost impossible to discern wether what you will be reading is relevant. Do expect loss of hair during this install.”
on September 10, 2008 at 1:18 pm cika

one things left if i see your pic SQL2008.AdventureWorks_All_DB_Scripts.x86.msi has 80495K but i downloaded it n it has SQL2008.AdventureWorks_OLTP_DB_v2008.x64.msi
source code, 44740K, uploaded Aug 7 – 1240 downloads

getting more confuse…did i miss something?!?
on September 17, 2008 at 1:07 pm Sam

Thanks a lot.

Absolute newby on SQL SERVER and the sample db’s are a nightmare to install when just statring out.

Spent hours trying to work out what I was doing wrong, I did manage to get the blank db’s install then spent ages trying to figure out why empty.

Eventually found a post with a link to this, should really make it a “sticky” on codeplex forum. Luckily I was balding before I started to try and install.

On a serious note for any newby’s something which should be so simple, quick and easy has seemingly been made very complex, hard and long when a guide similar to this is all thats needed.
on September 22, 2008 at 8:56 pm Daniel Freeman

Many Thanks !!!

Your site is at the top of the list of my “Favorites”. This is the go to place for solutions.
on September 24, 2008 at 1:21 pm nancy

I followed Method 2 to restore database back up,I did the same procedure but I’m getting following error at the end of final step,

System.Data.SqlClient.SqlError:The path that is specified by

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks.Documents’ cannot be used for FILESTREAM files bacause it is not on a supported system.(Microsoft.SqlServer.Smo)

What could be the reason?
on October 18, 2008 at 10:53 pm Roger Muggli

I am using sql express and I don’t have the server management studio. I can’t seem to get sqlcmd to work because it can’t find the dql script even though sqlcmd and the script are in the same directory.

Any suggestions would be welcome.

Roger
on October 22, 2008 at 1:26 am siltd

I am using 2008/Express. A little different from your screenshots, but close enough I could figure this out.

Like many others because it was an MSI I wasn’t expecting to do anything once it finished.

The biggest clue was to click start.
on November 13, 2008 at 1:38 pm Etsegenet

hello,
when I try to install the setup I get this error message

PrepInstance() failed for MSSQL$SQLSERVER2008. the following features are missing: Full Text Search fix the problems and re-run setup

please tell me how to fix it.
thanks
on November 20, 2008 at 4:14 pm Sabrena

Works like a charm for SQL Server 2008!
on November 23, 2008 at 11:00 pm SQL SERVER - 2008 - Download and Install Samples Database AdventureWorks 2008 Journey to SQL Authority with Pinal Dave

[...] For installation instructions please refer my previous article here. [...]
on December 25, 2008 at 8:30 pm Kalpesh

Hi Pinal,

When I am trying to install sample database got error “full text search feature missing” Install first that and installation
didn’t complete (Roll back)

Please let me know what to do

Regards
on January 2, 2009 at 8:39 pm MC

I have the same problem.. Did you get an answer?

Regards,

MC
on January 5, 2009 at 12:18 pm Salil

I also have the same problem. Full Text Search is installed.

Best Regards,
Salil.
on January 5, 2009 at 12:49 pm Salil

Hi,

The Full Text Search service was stopped , I had to enable it from the
Sql Server Configuration Manager -> Sql Server Services -> Sql Full Text Filter Deamon Launcher ( MSSQLSERVER ).

I installed SQL2008.AdventureWorks_All_Databases.x86.msi and it worked for me.

Best Regards,
Salil.
on January 30, 2009 at 5:41 am Jay

Hi,

I have installed MS SQL server 2008 express version. I tried to install the Adventureworks sample database from the site you mentioned.

I went through the installation process and at the end it gives the following error

——————————————————————
PrepIndstance() failed for MSSQLSERVER
The following feature are missing : Full Text Search
Fix the problem and re-run the setup

——————————————————————-

I have installed the SQLserver 2008 express on Vista.

So how would I enable the ‘Full Text Search’ ?
Do I have to install it or have to change any swith?

Please let me know,
thanks,
Jay
on January 30, 2009 at 5:49 am Jay

Hi,

I don’t see the following in my configuration manager,

‘Sql Server Configuration Manager -> Sql Server Services -> Sql Full Text Filter Deamon Launcher ( MSSQLSERVER )’

so i cannot enable it. Does that mean I have to install it?

thanks,
Jay
on February 24, 2009 at 3:50 am Ed

Hi,
You’ll need to go to windows services and set the following service from disabled to manual then start it:
SQL Full-test Filter Daemon Launcher(MSSQLSERVER).

if you don’t have that installed rerun MSSQL 2008 and make sure you see the full-text search feature enabled…
on February 26, 2009 at 5:55 pm Danny

Pinal,

I’ve googled this for many days and have not found an answer.

When installing SQL Server Express 2008 with Advanced
Services using bootstrapper or running the exe directly
I receive ‘the downloaded file is corrupted’ or .net framework JIT error. Management Studio 2005 installs fine
but 2008 fails to install.

Thanks in advance for your assistance.
on March 12, 2009 at 10:12 pm Cedric

Follow these steps to fix the ‘PrepInstance() failed for MSSQLSERVER’ issue…

1. Open SQL Server Configuration Manager

2. Right-click on SQL Full-text Filter Daemon Launcher(MSSQLSERVER) and select ‘Properties’

3. Click on the ‘Service’ tab

4. For Start Mode, select ‘Automatic’, then Apply and OK

5. Click on the Start button
on March 24, 2009 at 6:32 pm Alfie

I have been trying to install the Sample Databases for SQL Server 2008 for days and can’t seem to find any relevant information!

I don’t seem to have and .bak files anywhere? If they are not there how can I do a restore? Is there another way round this? Can I run the SQLScript in the database folder?

Any help much appreciated! :)
on March 27, 2009 at 3:11 am Samastr

Ive have the same problem. There is no AdventureWorks.bak file? All other files are present. (it did ask me to debug 5 times during the .msi install.

When trying option 1 instead of the restore option, i fail to find the @data_path in the instawdb file.

A double block for me.

What can i do?????? please help
on March 31, 2009 at 6:18 pm Jigar

hey hello pinal sir…

i m trying to install SQL srver 2008 since a log….
but always got stucked at one point….

actully it wants ACCOUNT NAME and PASSWORD for all analysys,reporting and fulltext filter FDHOST services…

but i m not getting tht asking for which account name and password….

can u assist me for tht…
and suoopes i have no account created for thn is there any default account name and password can be used in place..
please provide me the help…

waitintg for response…
is anyone else thn pinal sir have same que and have any solution thn request to help.
on April 1, 2009 at 6:30 am Imran Mohammed

@Jigar,

Why don’t you create a local account.

start – (right click) My computer select Manage -Pop up will open – Expand Local Users and Groups – Right Click Users Click New User and provide some basic details and thats it, you will have a local user A/c.

I have just seen screen shots for SQL Server 2008 installation, But in those screen shots I did not see any where it mentioning default built-in administrator account ( 2005, 2000). So I don’t think you have any other option but to create a local or domain user account and have it given appropriate privileges to run that service.

Screen Shots Step by Step Installation of SQL Server 2008:
Link : http://experiencing-sql-server-2008.blogspot.com/2008/09/step-by-step-installation-of-sql-server.html

Regards
~IM
on April 1, 2009 at 9:25 am pinaldave

@Jigar,

You can visit my previous article : http://blog.sqlauthority.com/2008/06/12/sql-server-2008-step-by-step-installation-guide-with-images/ for additional information as well.

@Imran,
As usual, I appreciate your comments. You have been most helpful person on this domain.
on April 29, 2009 at 5:54 pm deepak

sir i read your installation guide, i installed it correctly and i found no error but while using i found only system tables. while using method 1 i was unable to find @data_path and i tried for 2 method but i found back file in the folder can u hhelp me out
on May 22, 2009 at 7:03 pm Mark Zakarv

I followed these steps in the link to install Sample database ino SQL server 2008. In Step 1. I can NOT find @data_path in instawdb.sql. Aslo when I when this script, I got
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near ‘:’.
but ” :on error exit” is correct syntax.

Because Method 1 fails, I chooose Method 2. There is no *.bak file under /100 and its subdirectories.

Please give some help. Thank you
on June 5, 2009 at 12:58 am Mingle Linch

Samething here. I did not find @data_path variable. I downloaded v2008.x86.msi

It also contains the following comments in the script -

– Be sure to enable FILESTREAM before running this script!
– Be sure to enable FULL TEXT SEARCH before running this script!

–>> WARNING: THIS SCRIPT MUST BE RUN IN SQLCMD MODE INSIDE SQL SERVER MANAGEMENT STUDIO. <<–
:on error exit

/*
* NOTE: These environtment variables are set by the installer package (MSI).
* In order to run this script manually, either set the environment variables,
* or uncomment the setvar statements and provide the necessary values if
* the defaults are not correct for your installation.
*/
–:setvar SqlSamplesDatabasePath "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\"
–:setvar SqlSamplesSourceDataPath "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\"
on June 5, 2009 at 2:09 am Mingle Linch

Sorry, it’s all set. The AdventureWorks database is already installed. It looks like I don’t need to do the rest of steps. Thanks.