SQL Server 2008 – Installing the AdventureWorks Sample Databases

About the Databases

There are several versions of the AdventureWorks databases out there.  There are versions for SQL Server 2000, 2005 and 2008, but this post is concentrated on the 2008 versions.  For SQL Server 2008, there are 3 databases.

  • AdventureWorks OLTP – A standard online transaction processing database containing a year of data of data for a fictitious bicycle manufacturer (Adventure Works Cycles). This database is useful for demo scenarios, and includes data for Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources functions.
  • AdventureWorks DW -  A data warehouse database containing a year of data in a star schema, containing multiple fact and dimension tables.  This database is excellent for constructing SQL Server Analysis Service (SSAS) OLAP cubes and demonstrating the Business Intelligence (BI) capabilities of SQL Server and BI client technologies like MOSS 2007, Reporting Services, and Excel.
  • The AdventureWorks LT (Light) - A highly simplified and smaller sample database which is helpful for those who are new to relational database technology.

Preparing for the Installation

Before attempting the installation, ensure the following services:

Full Text Search
Ensure you have Full-text Filter Daemon Launcher Service running. NOTE: You may receive an error stating that FILESTREAM is not enabled during the install if this service is disabled, which is a bit misleading.

FILESTREAM
If you did not enable FILESTREAM services during the SQL Server 2008 install, you’ll need to light it up prior to installing the sample AdventureWorks OLTP and DW databases.  If you don’t, the AdventureWorks database installation will fail trying to restore the database, and will warn you that FILESTREAM needs to be enabled. To enable FILESTREAM services on SQL Server 2008, see my post, How To Enable FILESTREAM Feature On SQL Server 2008.

Installing the Sample Databases

  1. Download the msi installer for SQL Server 2008 AdventureWorks databases you want to install from CodePlex
  2. Ensure you have Full-text Filter Daemon Launcher Service running
  3. Ensure you have FILESTREAM services enabled on your SQL Server 2008 server (see notes above)
  4. Run the msi installer and click through the intro and license screens
  5. Turn on the restore database options during the install, as shown below.  This option will automatically restore the databases, but it is turned off by default. 
  6. Select the database server and click Next to complete the installation
  7. Repeat steps 1 through 6 to install the DW BI and/or LT databases databases
  8. If you did not turn on the restore database options (step 5), you will need to attache the database manually after the install is complete.  You can attach them by running database restore SQL scripts located in C:\Program Files\Microsoft SQL Server\100\Tools\Samples\.

Restore Database Options

Notes From the Installation Wizard

The following notes are displayed in the wizard when you attempt to restore the database:

Select the name of the local instance to restore to from the dropdown list provided.

Note: The AdventureWorks databases will not be automatically removed during uninstallation.

The AdventureWorks databases can be automatically installed ONLY to a local instance of Microsoft™ SQL Server™ 2008, and both Full-Text Search and FILESTREAM must be installed and enabled if the AdventureWorks2008 OLTP database is included.

If required, the selected instance will have FILESTREAM automatically enabled, and it will be restarted automatically as necessary.

Warning: Both FILESTREAM and Full-Text Search must be installed and enabled for the AdventureWorks2008 OLTP database to be installed successfully.

If you choose to install the AdventureWorks databases automatically (as determined by the selected features of this installer package), the data files for the AdventureWorks databases will be created in the same directory as the data file (MDF) for the master database.

The AdventureWorks databases can be restored to another server by copying the BAK file to that server and restoring the database there with SQL Server Management Studio or the included T-SQL scripts.

For more information visit: http://go.microsoft.com/fwlink/?LinkId=87843


  del.icio.us it! digg it! reddit! technorati! yahoo!

About these ads

16 responses to “SQL Server 2008 – Installing the AdventureWorks Sample Databases

  1. thanks for the instructions.
    did exactly as above. i have both the filestream set and the text search service running. running the installer for the adventureworks2008 msi finishes fine, but where is the database? there is nothing in the list of databases apart from master model etc. is there a log file that gets written somewhere?

  2. For the databases to be restored during the install, you have to turn on the restore database options (in the Custom Setup options of the install). This option will automatically restore the databases during the installation, but it is turned off by default.

    You can attach/restore them manually. If you did not turn on the restore database options (step 5), you will need to attache the database manually after the install is complete. You can attach them by running database restore SQL scripts located in C:\Program Files\Microsoft SQL Server\100\Tools\Samples\.

  3. Pingback: SQL Server 2008 - How To Build and Deploy AdventureWorks OLAP Cube « Stuart Cox’s Tech Punch

  4. Pingback: SQL Server 2008 - How To Build and Deploy AdventureWorks OLAP Cubes « Stuart Cox’s Tech Punch

  5. I was able to completely and successfully install the LT and DW databases. However, the OLTP crashed on each of 5 trials. Three trials before restoring the LT and DW, and 2 after those were successfully installed. The error message given is the same each time:

    Error -2147217900: failed to execute SQL string, error detail: RESTORE DATABASE is terminating abnormally., SQL key: SqlString_RestoreAdventureWorks2008 SQL string;
    IF ’1′ = ’1′
    BEGINUSE master; DECLARE @sql_path nvarchar(2,,,

    Then I can only click OK and finish which creates a rollback and the message that no changes or updates were made to the Server.

    I tried to download the OLTP again, with the same results. Is there an error in the OLTP that needs to be corrected in order for this install to take place cleanly?

  6. I haven’t had any issues restoring the databases using the script. Were you running the SQL scripts manually, or letting the install do it? I’d recommend turning on the option to let the installation do the restore, as mentioned in the post.

    Also, please note, there are no inter-dependencies between the databases themselves. You can install any one of them individually of the others. If you’re installing multiple sample databases, there are no restrictions on which one you need to install first, etc.

    Just make sure you’re installing databases for the right version of SQL Server. SQL Server 2000 and 2005 sample databases should work fine on SQL Server 2008, but the databases are not backward compatible, as they are designed to show off new features of the new versions of SQL Server.

  7. Thank you for the great instruction about installing Adventureworks – should be on the codeplex site. It’s sad to see that msft is not able to make a better installer – why didn’t they check the FILESTREAM option before trying to install it?!

    Btw, after enabling FILESTREAM in Configurationmanager I still needed to enable FILESTREAM in SQL Server Management Studio
    - Select Instance, right click Properties
    - Select Advanced Page and set the first option ‘Filestream Access Level’ to ‘Transact-SQL access enabled’
    - Restart instance

  8. I didn’t have any success with the “step 5″ of having the installer do the restore step for me. I went ahead with the manual approach, so if the steps for that are helpful for anyone you can find them here:

    http://codeslammer.wordpress.com/2008/10/05/sql-server-2008-sample-databases/

  9. Pingback: Reed Me : More AdventureWorks in the blogosphere...

  10. I also had to check the box to enable remote clients on the FILESTREAM issue, otherwise I still got the “PrepInstance()” error message. SSMS and SQLServer are on the same system.

  11. I keep receiving an error saying that the samples.cab file is corrupt. I’ve downloaded the installer several times. Any help would be appreciated.

    • Are you doing this on a Virtual PC? You may have corrupted the cab file if you copied the file from your host PC to the VPC using a Shared Folder. VPC shared folders will corrupt large files. To fix it, download the cab directly to your VPC or copy it over a network share. Check out my post here for more information .

  12. Thanks, I’ve got past the problem of the cab file.

    Now when i run the msi i don’t get an option to enable the restores. It appears to be missing.

    Setup: Virtual PC 2007
    Windows Server 2008 RC2
    I’ve tried pretty much every msi for AdventureWorks

  13. Chris Kirschner

    The absence of any info re installing – configuring or whatever is necessary to have full text search makes it impossible for a beginner to begin. saying you need the filter daemon launcher running doesn’t help without an instruction – at least a hint – of how to accomplish that.

  14. Good day to all. Here’s the solution on How to install the AdventureWorksDb2008.

    1. Install SLQServer2008
    2. Check all the features(Specially the Full Text Features)
    3. After installing the SQLServer2008, install the SQLEXPRESS_Advance
    4. After installing everything, go to the services and enable the SQL Full Text Features. Set it to automatic and start the service.
    5. Run SQL Server Configuration Manager and you will be able to find the SQL Server Services. In the SQL Server Services, Right Click SQL Server (SQLEXPRESS), click properties, then go to FILESTREAM tab, and enable the the FILESTREAM for T-SQL access. no need to check the other. then click OK
    6. start your SQL management Studio
    7. You will not be able to find the AdventureWorks database at first. what you need to do is to attach the database. the database can be found in the instancename folder.

    Hope this willhelp.

  15. Pingback: Menginstall AdventureWorks2008 Sample Database - Haer Talib

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