Back to: SQL Server DBA Tutorials

How to Download and Restore AdventureWorks Database in SQL Server

in this article, I am going to discuss How to Download and Restore AdventureWorks Database in SQL Server. We need some demo data i.e. demo database to work with, and I’m going to use the SQL Server standard database i.e. AdventureWorks database. First, we will discuss How to download AdventureWorks sample databases, and then we will discuss the steps for restoring them to the SQL Server database using SQL Server Management Studio. 

How to Download AdventureWorks Database?

Let us see how to download the AdventureWorks database. Open any web browser and search for AdventureWorks Database Download and click on the below Microsoft link.

Once you click on the above link it will open the following download AdventureWorks sample databases page.

If you scroll down, you will see that there are different versions of the bak file available as shown in the below image. I am going to download the 2014 .bak version.

If you notice we have three different types of .bak files. They are as follows.

1. OLTP: OLTP data is for most typical online transaction processing workloads.
2. Data Warehouse: Data Warehouse (DW) data is for data warehousing workloads.
3. Lightweight: Lightweight (LT) data is a lightweight and pared-down version of the OLTP sample.

Why 2014 bak file? Why not the latest bak file?

Now, you may have one question, why we are choosing the 2014 bak file? Why not the latest versions i.e. 2019, 2017, or 2016?

Well, if you have a Windows 7 computer, then you have installed SQL server 2014, and you don’t want to download a 2016 or 2017 version. And some people reading this article won’t be able to use it. However, if you’ve got the latest version of the SQL server, be it 2017, 2019, or even later, you will be able to use the AdventureWorks 2014 database. So, the point is very simple with an older SQL Server database, we cannot use the latest bak file but with the latest SQL Server Database, we can use the old bak file. Even it is up to you. As per the database installed on your machine, you can choose the bak file. If you want to go with me, then please click the AdventureWorks2014.bak file under the OLTP version as shown in the below image.

Once you click on the above link, it will take a few seconds to download the AdventureWorks2014.bak file as shown in the below image.

How to Restore AdventureWorks2014.bak file to SQL Server Database?

Once you download the AdventureWorks2014.bak file, next we need to restore it to SQL Server. You can use the .bak file to restore the AdventureWorks database to your SQL Server instance. You can do this using the RESTORE (Transact-SQL) command, or using the graphical interface (GUI) in SQL Server Management Studio. Let us first see how to restore the AdventureWorks database using SSMS.

Open SQL Server Management Studio and connect to the SQL Server instance where you need to restore the same database bak file. Here, we are going to use Windows Authentication and then we need to provide the Server name and then need to click on the Connect button as shown in the below image.

Once you click on the connect button, you will be connected to SQL Server Instance with the following Microsoft SQL Server Management Studio interface.

We got lots of pluses and minuses, and when I click on a plus, it expands it to another layer down. And we’ll be going through quite a lot of this course. Actually, this is the heart of what being a DBA is all about. Now, if I open up Databases, you’ll see that we’ve got System Databases. If you’ve got the more recent version of SQL Server, you’ll also have Database Snapshots as shown in the below image.

How to Restore the Database using SQL Server Management Studio?

In our next article, I will show you the different options to restore the database. In this article, I am going to show you the simplest way to restore a database using SSMS GUI. So, basically, we are going to restore the AdventureWorks2014.bak file that we have just downloaded. The following are the steps to restore a database.

First, right-click on databases and then select the Restore Database option from the context menu as shown in the below image.

Then it will open the following Restore Database window. From the left-hand menu, select the General tab, and then select the Device Radio button as we are going to restore the database from the device (bak file saved in our computer) and then select the three (…) browser button to select the bak file as shown in the below image.

Once you click on the three-dot (…) browse button, it will open the following Setup Backup Device popup. Here, first, we need to select the Backup Media Type as File, the other option is URL. Then we need to click on the Add button as shown in the below image.

Once you click on the Add button, it will open the following Locate Backup File popup. I have saved the bak file in the D:\DBBackup location. So, you just need to select the location where you saved the bak file in your machine and also you need to select the bak file. Then you need to select the extension as Backup Files and finally, you need to click on the OK button as shown in the below image.

Once you click on the OK button in the previous step, you will see that the selected bak file is added into the Backup Media of the Selected Backup Devices windows. Here, you just need to click on the OK button as shown in the below image.

Once you click on the OK button, you will be back to the Restore Database window. Here, if you want you can change the name of the database. I have changed the database name from AdventureWorks2014 to AdventureWorks. If you want you can go with the same name as AdventureWorks2014. And finally, click on the OK button as shown in the below image.

Once you click on the OK button, it will restore the database. On successful restoration, you will get the following message. Click on the OK button to close the Database Restore window.

Now, in SSMS Object Explorer, you can see the AdventureWorks database as shown in the below image.

In this database, there are lots of tables, views, stored procedures, stored functions, etc available which we will be going to discuss in our upcoming articles.

In the next, and a few upcoming articles, I am going to discuss How to Restore and take Backup with different options in SQL Server which are more important to understand as a SQL Server Database Administrator. Here, in this article, I try to explain How to Download and Restore the SQL Server AdventureWorks database using SQL Server Management Studio and I hope you enjoy this How to Download and Restore SQL Server AdventureWorks Database article.