In this article, I have explained from where you can download AdventureWorks sample database and congifure it to use in local SQL server database. Basically, we will be restoring the AdventureWorks sample database in our local SQL server.

Table of Contents

Step 1: Download AdventureWorks database

So, this is our first step to download complete sample database of AdventureWorks, there are many SQL server versions of the sample database.

You can download .bak file of your preferred version.

I will be downloading AdventureWorks2016.bak for this tutorial. It is around 46.7Mb in Size.

Other versions are:

AdventureWorks2022.bak (AdventureWorksLT2022.bak)

AdventureWorks2019.bak (AdventureWorksLT2019.bak)

AdventureWorks2017.bak

AdventureWorksLT2017.bak

AdventureWorks2016.bak

AdventureWorksLT2016.bak

AdventureWorks2016_EXT.bak (125MB) This is an extended version of AdventureWorks, designed to showcase SQL Server 2016 features

AdventureWorks2014.bak

AdventureWorks2012.bak

Once you have downloaded it, open the "Downloads" Folders (Where file is downloaded) and copy/paste it in root folder of D: or E: Drive, basically we are doing this, so we don't get any error related to protection rights.

Lightweight (LT) data is a lightweight and pared down version of the OLTP(online transaction processing workloads) sample.

Step 2: Open your SQL server Management Studio and Restore .bak file

Once you have downloaded the above file, open your local SQL server Management Studio, using which we will be restoring the above downloaded .bak file.

Now, right-click on "Databases" and Select "Restore Database"

database-right-click-restore-database-min.png

From the new pop-up, select "Device"-> "Browse" -> "Add" ( From new pop-up)

Click on "Add"-> Navigate to "E:\AdventureWorks.bak" and Select it, Click "Ok" and the again click "Ok"

Once the restore is ready, you will screen like below

restore-adventuraworks-sample-database-min.png

Click OK and Database will be restored completely.

Note: If your SQL server version is lower than AdventureWorks Sample database version, it will throw error. For example if you are trying to install AdventureWorks2017.bak on SQL server 2016 or 2012 then you will get error and you will not be able to restore it. To Check your SQL server version check here

Here is the Gif Image of complete process

download-restore-adventuraworks-sample-database-min.gif

Add Sample database using .BAK and T-SQL

If you don't want to use above GUI method, you can directly run the T-SQL query

Suppose, your AdventuraWorks.bak file is located in "E:\", then your SQL query would be as below

RESTORE DATABASE AdventureWorks2016 FROM DISK = 'E:\AdventureWorks2016.BAK' 
GO

If you are getting error when executing above command like below

Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQL2016RTM\MSSQL\DATA\AdventureWorks2016_Data.mdf" failed with the operating system error 3(The system cannot find the path specified.).

then you need to try to find the location of .mdf and .ldf file, so execute the below command

RESTORE FILELISTONLY 
    FROM DISK = 'E:\AdventureWorks2016.bak'

You will get location of .mdf and .ldf with logical names, use them as below

RESTORE DATABASE AdventureWorks2016 FROM DISK = N'E:\AdventureWorks2016.bak'  WITH  FILE = 1, 
 MOVE  N'AdventureWorks2016_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2016_Data.mdf',
MOVE N'AdventureWorks2016_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2016_Log.ldf',
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

then, executing the above command will work and it will restore AdventureWorks2016.bak file.

restore-using-t-sql-bak-min.png

Deploy on Azure using SSMS

In SQL Server Management Studio, to deploy a sample database from SQL Server to Azure SQL Database, follow these steps:

  • Connect to your SQL Server in SQL Server Management Studio.
  • If you haven't already done so, restore the sample database to SQL Server.
  • Right-click your restored database in Object Explorer -> Tasks -> Deploy Database to Microsoft Azure SQL Database
  • Follow the wizard to connect to Azure SQL Database and deploy your database.

That's it.

You may also like to read:

Restore (Import) database from .bak file in SQL server (With & without scripts)

How to back up SQL Server database ? (Various Ways explained)

Understanding SQL server switch case (With Example)

Free SQL Server Reporting Tools

SQL Comments (Comment in SQL Query)

Difference between SQL and NoSQL (SQL vs NoSQL)

What is SQL Query to get dates between two dates?

How to check if Columns exits in SQL Server table?