AdventureWorks Database Installation Steps


By:   |   Updated: 2021-06-18   |   Comments   |   Related: More > Testing


Problem

Microsoft offers multiple sample databases that can be installed on test instances and one of the most popular sample databases is the AdventureWorks database. This Microsoft SQL Server sample database is available for various workload types like OLTP, Data Warehouse, and Lightweight workloads. In this tip, I am going to show the steps to install the AdventureWorks database on your SQL Server instance.

Solution

AdventureWorks is one of most popular sample databases available for testing and learning. There are various versions (adventureworks2014, adventureworks2017, adventureworkslt, etc.) of this database that you can use as per your SQL Server installation version. I am going to show you how to install the free sample database that you can host on any version of SQL Server and the latest version, AdventureWorks2019.

There are two ways to install sample databases:

  • Using Scripts
  • Restoring the backup (*.bak) file

Both approaches will be discussed in this tutorial.

Setting up SQL Server Database AdventureWorks using T-SQL Scripts

To get the scripts, go to this link AdventureWorks sample databases available for SQL Server development.

Scroll down to this section:

adventureworks scripts

There are two options and we will use the OLTP scripts.

After you download and open the zip file, you can see several csv files and a master T-SQL script file (highlighted below). The next step is to run this master T-SQL script instawdb.sql to create the sample database and all its associated objects.

adventureworks scripts

We need to ensure some of the prerequisites before going ahead and running above T-SQL script:

  • Full Text Search should be installed for your SQL Server Instance if you want to use this feature
  • Store all above downloaded files and scripts under C:\Samples\AdventureWorks
  • Run the SQL script instawdb.sql in SQLCMD mode

Check if Full Text Search is enabled by running the below query.

--Check whether Full text Search is Installed or not
SELECT  
  SERVERPROPERTY('IsFullTextInstalled') AS [Full Text Search Installed];  
GO

Output value of "IsFullTextInstalled" will return:

  • 1 means this feature is installed
  • 0 means it is not installed

If Full Text Search is not installed and you want to create full text objects then you need to install and configure Full Text Search. You can get the step by step process to install Full Text Search from here.

Store all downloaded files in C:\Samples\AdventureWorks location.

adventureworks scripts

Now we can run the instawdb.sql script.

  • Launch SQL Server Management Studio (SSMS)
  • Connect to your SQL Server instance in SSMS
  • Click File  > Open > File...
open sql file

Choose the SQL file instawdb.sql file to open it as shown below.

open sql file

Here you can see the script in query editor of SSMS.

query window

Let’s make sure SQLCMD mode is enabled before running the SQL script. Go to "Query" menu in SQL Server Management Studio and click "SQLCMD Mode" to enable it, the icon on the left should have a yellow box around it when enabled.

enable sqlcmd mode

Now we can hit F5 in the query window to execute the script.

I have taken various screenshots of different sections of the output to show you what all objects are being created as part of this sample database creation. You can see full text catalog and indexes are being created in the below screen. If you have not installed full text search then this portion of script fails with error stating Full Text Search is not enabled on this instance.

query output

Another section of output which shows multiple tables have been created and later these tables have been loaded with some data as shown below.

query output

Once the script completes, you can check the database in SQL Server Management Studio by hitting the refresh button in the Databases folder. I have expanded the newly created database "AdventureWorks" as shown below.

adventureworks database

Setting up AdventureWorks2019 using Backup File on SQL Server 2019

Another approach to create a sample database is by restoring the database backup file (*.bak file) on your SQL Server instance. You can download the backup file from here - download AdventureWorks sample databases or from GitHub.

Browse to the section named "Download backup files" as shown below.

download adventureworks backup

Click any file you want to download and save it to your target location. Below we downloaded AdventuresWorks2019.bak.

downloaded backup file

The next step is to restore this backup file to your SQL Server instance.

Launch SQL Server Management Studio (SSMS). Connect to SQL Server instance where you want to install this sample database. Right click on "Databases" and click "Restore Database…".

restore database

The below window will open, select the backup file by clicking the Device radio button and then click ... to select the backup file.

restore database

Now, click the "Files" tab to ensure the database files and path are correct. Once done, click OK to start the restore process.

restore database

Your sample database will be restored in the above step and you can go ahead and start using this database.

If you want to use a T-SQL statement to restore the sample database instead of the SSMS GUI, you can use a script similar to below.

--Restore sample database AdventureWorks2019 using RESTORE DATABASE T-SQL Statement--Change DB name and path as per your choice
USE master
Go
RESTORE DATABASE [AdventureWorks2019]
FROM DISK = N’C:\Manvendra\Sample-DB-AdventureWorks\AdventureWorks2019.bak’
WIWITH FILE = 1, 
     MOVE N’AdventureWorks2017’ to N’C:\MSSQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019.mdf’,
     MOVE N’AdventureWorks2017_log’ TO N’C:\MSSQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_log.ldf’
     NOUNLOAD, 
     STATS = 10
GO

Here, I have executed the above T-SQL statement and the database has been restored.

restore database

The AdventureWorks2019 has been restored to the SQL Server instance as per the below screen. You can see both sample databases that were created in the below image

restored advnetureworks databases
Next Steps





get scripts

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips


Article Last Updated: 2021-06-18

Comments For This Article





download














get free sql tips
agree to terms