Installing Sample Databases for SQL Server 2008R2

As I work on testing the Database Health Reports project, I have configured several test servers running as virtual machines using Hyper-V. Part of setting these up for testing, I usually end up installing the sample databases for SQL Server. The sample databases give me something to test against, and what usually ends up happening is I end up polluting the sample database over time with lots of test tables, bad indexes, and all the stuff that you should keep in a test database, and never actually put in a production system. Occasionally I need to wipe the server clean and start over, so when I do this I usually end up re-installing the sample databases. I do this just often enough to forget exactly where to get the sample databases from. To solve that problem I have created this blog posting so that I have somewhere to look next time I need the sample databases.

Step 1. Download the sample files from Codeplex.

There are a couple different versions of sample files available. I usually use the AdventureWorks 2008 OLTP Script. The OLTP (OnLine Transaction Processing) sample doesn’t include the data warehouse samples, and it is just quick and easy to use.
Download from here:
http://msftdbprodsamples.codeplex.com/releases/view/89502
AdventureWorks is the name of the sample database, which replaces the old Pubs database from earlier versions of SQL Server, and the NorthWind sample database from SQL Server 2005.
Download the a zip file containing the scripts needed to install AdventureWorks. The filename should be AdventureWorks 2008 OLTP Script.zip.

Step 2: Run the scripts.

To run the scripts either unzip the file and save the contents somewhere.

Then browse into the AdventureWorks 2008 OLTP Script directory.

Open the instawdb.sql script with SQL Server Management Studio.

You will want to enable Full Text Search before running the script.

SQLCMD mode will need to be enabled to run the script. If the script is run without SQL Command mode, the following errors will be generated. To Enable SQLCMD mode, choose the SQLCMD Mode option from the query menu in SSMS.

If the script is run without setting up the directories correctly the following error will be displayed.

A fatal scripting error occurred.
Variable SqlSamplesSourceDataPath is not defined.

To fix the error, just set up the directories edit the 2 lines at about lines 36 and 37, first uncomment them, then change the directories to match the directory of your SQL Server, and the directory that the samples were downloaded to as shown in the following directory.

Once the directories have been edited, then just run the script. The script may run for a few minutes depending on your server speed. If the script runs successfully it will generate the following output.

Then refresh the database tree in the object explorer and the AdventureWorks2008 database will show up. Go ahead and browse the tables and take a look around.

Not exactly a straightforward process, but if you follow the steps you will get The AdventureWorks 2008 database running on SQL Server 2008R2.
Enjoy

 

---

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!