Installing AdventureWorks Sample Database

Now the SQL Server and the management studio are ready for using. For training purpose, we need a database with some sample data. It will be very helpful to have a sample database while learning SQL Server. For this purpose, Microsoft has introduced the AdventureWorks Sample Database. Currently, AdventureWorks sample database is available in CodePlex as open source.

What Is AdventureWorks Sample Database?

The AdventureWorks database is a sample database initially introduced by Microsoft for beginners to learn SQL Server with the sample data and database objects like the tables, indexes, triggers, stored procedures, functions, etc. It is meant for standard online transaction processing scenarios. This sample database and the data is developed for a fictional bicycle company called Adventure Works Cycles. The scenarios included in the sample database are manufacturing, product management, sales, purchasing, contact management and human resources. This database will be helpful for those who are new and learning relational database management systems (RDBMS).

Every new version of SQL Server has a corresponding version of sample database. This is because the newer versions of SQL Server has new features. These new features are incorporated in the sample database and released as a new version few days after the SQL Server release.

Choosing The SQL Server Sample Database

As of now I have SQL Server 2014 Developer Edition installed on my computer. So, I’ll walk you through the installation process of AdventureWorks 2014 Sample Database. Once SQL Server 2016 is available for general public and AdventureWorks 2016 is also available, I will post a new article on AdventureWorks 2016. (NOTE: if you are looking for a sample database for SQL Server 2016, then use “Wide World Importers” sample DB. It will have the data and the objects needed for experimenting SQL Server 2016.)

AdventureWorks 2014 itself has different types of sample databases for different editions of SQL Server 2014.

Types of Sample Databases

• AdventureWorks OLTP: The general AdventureWorks sample which is commonly used in SQL Server database engine.
• AdventureWorks DW: This is the Data Warehouse sample database useful for playing with Business Intelligence features of the SQL Server. If you have installed SQL Server Developer edition or Enterprise or Business Intelligence editions of SQL Server then you can use the AdventureWorks Data-warehouse sample database.
• AdventureWorks Tabular Model: Sample tabular model solution for analyzing the data using Analysis Services. This database is good for learning DAX queries.
• AdventureWorks Multidimensional Model: Sample multidimensional model solution for analyzing the data using Analysis Services. This is very useful for learning SSAS/MDX query fundamentals.

For a beginner learning SQL Server as a relational database management system, you may need the AdventureWorks OLTP sample.

Downloading & Extracting AdventureWorks Sample Database

1. You can download the sample databases for SQL Server 2014 from CodePlex. There you can see all the different types of sample databases available for download.
2. From the list, download the Adventure Works 2014 Full Database Backup.zip below the Recommended Download Section. This is the sample database useful for learning SQL Server basics.
   
3. Once downloaded, unzip the file to extract the sample database backup file named AdventureWorks2014.bak.
4. Place the backup file (AdventureWorks2014.bak) under the default SQL Server 2014 backup folder.
   • On 64 bit operating system, the default backup folder will be like C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\.
   • On 32 bit OS, use C:\Program Files (x86)\Microsoft SQL Server\…..
5. Now, follow one of the below methods to restore this backup file to the SQL Server.

Installing or Restoring The AdventureWorks Sample Database

A. Restore Sample Database Using SQL Scripts

1. Login to the SQL Server Management Studio (SSMS).
2. Open a new SQL Query Editor window
3. Copy the below code and paste it into the query editor window

   USE master
   RESTORE DATABASE AdventureWorks2014
   FROM disk = 
   'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak'
   WITH MOVE 'AdventureWorks2014_data' TO 
   'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.mdf',
   MOVE 'AdventureWorks2014_Log' TO 
   'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.ldf',
   REPLACE
   

4. Click the Execute icon in the toolbar. You will see the status message as in the below picture once the database is successfully restored.
   
5. You can see the restored sample database in the Object Explorer under Databases folder.
   

B. Restore Sample Database Using SSMS GUI

1. Login to the SQL Server Management Studio (SSMS).
2. In the Object Explorer, right-click  the Database folder and select Restore Database...
   
3. In the Restore Database screen, choose Devices and click the ellipsis button to launch the backup device selection screen. In the device selection window, press Add button to launch the file dialog. The file dialog will open the default SQL Server backup location. As we have already placed the backup file in that location, just select the backup file in the file dialog and press OK. Again press OK in the device selection window.
   
4. Now the restore database window is filled with the details of the database to be restored from the backup.
   
5. Press the OK button in restore database window. The sample database backup is restored as a new database AdventureWorks2014. You can see the restored sample database in the Object Explorer under Databases folder.

In my future article on SQL Server Basics series, I’ll be using this sample database for training.

Reference

• AdventureWorks on CodePlex at https://archive.codeplex.com/?p=msftdbprodsamples.