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:
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.
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.
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…
Choose the SQL file instawdb.sql file to open it as shown below.
Here you can see the script in query editor of SSMS.
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.
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.
Another section of output which shows multiple tables have been created and later these tables have been loaded with some data as shown below.
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.
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.
Click any file you want to download and save it to your target location. Below we downloaded AdventuresWorks2019.bak.
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…".
The below window will open, select the backup file by clicking the Device radio button and then click … to select the backup file.
Now, click the "Files" tab to ensure the database files and path are correct. Once done, click OK to start the restore process.
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.
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
Next Steps
- Use the above methods if you want to setup other sample databases.
- You can also read more article on SQL Server Management Studio
