Finding the Correct Version of the AdventureWorks SQL Server Sample Database


The AdventureWorks database is a fictitious company database that has existed since SQL Server 2005 as a means to show new functionality in each new version released.  Because the Books Online TSQL samples leverage this sample database, AdventureWorks has become a vital aspect of learning new features/constructs within SQL Server.  What started as a big improvement over the Northwind and Pubs sample databases has evolved into numerous specialized off-shoots highlighting different aspects of the product.  There are now multiple versions of AdventureWorks databases for each version of the product, including many updates corresponding to service pack releases.  The end result is that it is extremely confusing to find the "correct" version of AdventureWorks that you are searching for.  I'll lovingly refer to this as AdventureWorks sprawl and try to lower the barrier to entry for getting started working with the AdventureWorks sample database.

Pre-SQL Server 2005:

AdventureWorks did not exist before SQL Server 2005.  In this time, the sample databases Northwind and Pubs were included on the media along with the product and could be installed during the setup process.  You can download the Northwind and Pubs sample databases for SQL Server 2000 here.

SQL Server 2005:

In the SQL Server 2005 timeframe, the AdventureWorks sample DB was introduced and also included on the media along with the product.  You can install the samples during setup or download them from the following location:

http://msftdbprodsamples.codeplex.com/releases/view/4004

The installers for SQL Server 2005 supply you with the .mdf/.ldf files, but you need to manually attach them to your instance for use.  You will see a couple different AdventureWorks databases, each with a slightly different focus.   For the majority of people downloading sample databases to learn T-SQL, refer to Books Online examples, etc., the AdventureWorksDB is the first one to start with.

SQL Server 2008:

Beginning with SQL Server 2008, the sample databases are no longer included with the media and must be downloaded and attached as a separate step.  The SQL Server 2008 sample databases are packaged slightly different than any other version, where they include a single .exe file that will allow you to install all different versions of the SQL Server 2008 sample databases.  They also include a .zip file containing a single .mdf and .ldf for attaching the basic AdventureWorksLT database.  I'd suggest unzipping this file and attaching the .mdf/.ldf to get started and using the .exe if you want to dig further into more complex examples.

http://msftdbprodsamples.codeplex.com/releases/view/37109

Step-by-step instructions for using the .exe to install all sample DBs is here.

SQL Server 2008 R2:

Beginning with the 2008 R2 sample databases, a new version was introduced that supported filestream.  If you are familiar with SQL Server and trying to learn filestream specifically, you'll want to download the version containing filestream sample schema/code.  For the huge bulk of users trying to leverage the sample database to learn the basics and extend into new areas, you will want to download the version without filestream support from the link below:

http://msftdbprodsamples.codeplex.com/releases/view/59211

In this link, you will see numerous versions of the database.  To get started quickly, I'd suggest downloading AdventureWorks2008R2-Full Database Backup.zip, unzipping the .bak file to your backup location, and restoring the database through SSMS.  The other versions all have different intentions for learning, but again I'd suggest starting with the basic version first before advancing to the other versions.

SQL Server 2012:

Through the evolution of the AdventureWorks DBs over the years, the site hosting the sample databases now includes 13 different links/flavors/versions of the sample database for SQL Server 2012 and can be found here:

http://msftdbprodsamples.codeplex.com/releases/view/55330

As with my suggestion for 2008 R2, I would suggest that you download AdventureWorks2012-Full Database Backup.zip, unzip the .bak file to your backup location, and restore the database through SSMS to get started quickly.

SQL Server 2014:

Following the same pattern as with 2008 R2 and 2012, you can download Adventure Works 2014 Full Database Backup.zip, unzip the .bak file to your backup location, and restore the database through SSMS to get started quickly.

http://msftdbprodsamples.codeplex.com/releases/view/125550

 

In summary, the AdventureWorks sample databases are outstanding learning tools for understanding examples from Books Online, trying out new constructs, and exploring entire new areas of SQL Server, but wading through the numerous versions and editions can be quite confusing.  If you have questions/comments, let me know.

Hope this helps,
Sam Lester (MSFT)

 

 

 

 

Comments (16)

  1. Mr. Wharty says:

    Great post and definitely something that needs to be a sticky on the SQL Server Samples and Community Projects forum.

  2. Shiobhando says:

    It really helps. Thanks.

  3. Really helpful post !!!

  4. pituach says:

    Nice post!

    Is there any information about the SQL 2014 version of AdventureWorks, like when it will be publish?

  5. Sam says:

    Thank god for your post, Googles, and even Codeplex seems to return the wrong version.

  6. Kam says:

    i downloaded Adventure Works for SQL sever 2012,  but when I tried to attach the file the file doesn't show up to be attached. Any suggestions or help would be greatly appreciated.

  7. Sam Lester (MSFT) says:

    Hi Kam, the 2012 download (AdventureWorks2012-Full Database Backup) is the full DB backup, so you need to run a restore as opposed to an attach.  Copy it to your backup directory and "restore database" either from SSMS or TSQL.

  8. Cédric FABIOUX says:

    I can't download, i'm having a "this file is malicous" error message.

    1. Hi Cédric, can you provide the specific link you are using?

      Thanks,
      Sam

  9. lori says:

    can you attach adventureworks 2012 to a SQL server 2014

    1. Hi Lori, yes, you can attach or restore the AdventureWorks 2012 database in a SQL Server 2014 instance. I just tested it out with the AdventureWorks2012-Full Database Backup.zip.

      Thanks,
      Sam

  10. Marty says:

    Hello Sam,

    Today is April 3, 2017. I've run into an issue while trying to follow along in Lesson 9 of a book I'm using (see Note below if interested) covering 'Self Joins'. In the 'Self Joins' portion, the book refers to the 'SupervisorID' column which should exist in the HumanResources schema in the Employee table. I don't see the 'SupervisorID' column and I've even looked for something like 'ManagerID'. Neither one exists. The SQL syntax from the book is below.

    I've downloaded the 'Adventure Works 2014 Full Database Backup.zip' file from the link on this page (http://msftdbprodsamples.codeplex.com/releases/view/125550).

    (Question) Would you be able to confirm if either the 'SupervisorID' or 'ManagerID' column should exist in HumanResources.Employee? I have a feeling the file I downloaded is not complete. There are other issues as well, but this one really stands out.

    SELECT Employee.BusinessEntityID,
    EmployeeInfo.FirstName AS EmployeeFName,
    EmployeeInfo.LastName AS EmployeeLName,
    SupervisorInfo.FirstName AS SupervisorFName,
    SupervisorInfo.LastName AS SupervisorLName
    FROM HumanResources.Employee AS Supervisor
    INNER JOIN HumanResources.Employee AS Employee    ON Supervisor.BusinessEntityID = Employee.SupervisorID
    INNER JOIN     Person.Person AS EmployeeInfo     ON Employee.BusinessEntityID = EmployeeInfo.BusinessEntityID
    INNER JOIN     Person.Person AS SupervisorInfo     ON Supervisor.BusinessEntityID = SupervisorInfo. BusinessEntityID

    Note: I'm currently stepping through the "Sams Teach Yourself T-SQL in One Hour a Day" by Alison Balter. Release Date: October 2015. ISBN: 9780134175997.

    Thanks, Marty

    1. Hi Marty, I think the SQL query is out of date. I did a quick check on AdventureWorks 2008 R2, AdventureWorks 2012, and AdventureWorks 2014. The ManagerID does not appear in the database anywhere. You can run the following query to check it out: select * from sys.columns where name like '%ManagerID%' -- This will return no rows. Try it with '%Person%' to see all of the columns in the DB that contain the string 'Person'.

      The following link refers to the SQL Server 2008 version of AdventureWorks having ManagerID, but I don't have that version installed on this machine. My hunch is that the update for 2008 R2 removed it.
      http://elsasoft.com/samples/sqlserver_adventureworks/SqlServer.SPRING.KATMAI.AdventureWorks/table_HumanResourcesEmployee.htm

      Good luck,
      Sam

      1. Marty says:

        Hi Sam, thanks so much for the prompt reply. I performed the tasks as you suggested and got the same results. I'm curious as to which AdventureWorks2014 file Alison Balter used when writing her book. Maybe she modified it. If so, I haven't come across mention of it yet.

        Thanks, Marty

        1. Hi Marty, sorry for the confusion. The AdventureWorks sample database changes from release to release. The syntax you are trying to execute (which contains ManagerID) hasn't been valid syntax since the AdventureWorks sample from SQL Server 2008, so the example from the book is out of date. Your options would be to either install the 2008 version of AdventureWorks and run the query against that or to simply skip this exercise from the book. It appears that this query is intended to show how to use a join on the same table (Employee table in this case) as a step toward understanding recursion in TSQL. The first example in the documentation from the "Recursive Queries using CTEs" section is pretty good. It has the table create statement and insert statements so you can play with the code yourself. https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

          Best of luck in your learning,
          Sam

          1. Marty says:

            Hi Sam,

            Thank you for the follow-up and the link to “Recursive Queries using CTEs”. It gives me insight into the hierarchy involved and its complexity. I hope to study this in depth so I can learn how to produce something like a corporate enterprise employee directory and its leadership hierarchy.

            The syntax in the book I'm using approaches this subject in a very different way. It does not mention CTE. It only refers to the process as 'Self-Joins'. From the language and examples used in the book, it gives me the impression the 'SupervisorID' column should already exist in the database and the column has already been properly populated with the leadership hierarchy in the 'HumanResources.Employee' table. In the SQL query, the aliases 'Employee' and 'Supervisor' are both created from 'HumanResources.Employee' and an inner join is used to join on the 'BusinessEntityID' and 'SupervisorID' columns. Just to see what would happen, I replaced the 'SupervisorID' column with the 'BusinessEntityID' column, and of course, the result set shows each employee is their own manager and no hierarchy exist.

            In any case, I will move along and circle back to study and review the material on Common Table Expressions (CTEs).

            Again, thanks so much! Marty

Skip to main content