When learning how to use business intelligence (BI) software, we can find many examples using text or Excel files. Microsoft’s Excel and Power BI also have the capability to connect to databases and extract data directly from them for analysis. Microsoft provides sample databases for download, and we can restore these databases to an SQL Server instance running on our desktop computer. This is a wonderful arrangement that allows us to explore some reasonably complex databases with powerful BI tools. In this article, we will go through the process of installing SQL Server, the SQL Server Management Studio, and restoring a sample database, in particular, the AdventureWorks database.

To take advantage of these databases, we need 3 pieces:

  1. Microsoft SQL Server 2019 software – this is the relational database server that will hold all the data tables
  2. SQL Server Management Studio (SSMS) – this software connects to the database server and allows us to manage the server and its databases
    • Note that this installer also contains the Azure Data Studio which can also be used to connect to the database server.
    • Both SSMS and Azure Data Studio allow us to write SQL queries against the database.
  3. One or more of the Microsoft sample databases – these are:

There are versions of all 3 databases for both a regular instance of SQL Server and for the Analysis Server if you wish to try that. We will focus on installing the databases into SQL Server for now. Below, we go through the steps to get each required piece running,  in particular, we will focus on the AdventureWorks database, but the process we go through will work for the others as well.

1) Installing Microsoft SQL Server

First, we need to download the Microsoft SQL Server 2019 software from the Microsoft site. As of this writing (September 2022) this is the latest stable version of SQL Server. This version of SQL Server will have mainstream support until Jan. 14, 2025, so I recommend using SQL Server 2019 to load these sample databases, for as long as the software version is available. Go to Microsoft’s SQL Server download page to find the version you want.

Scroll down to the bottom of the page to find the free versions, Developer or Express. For our purposes choose the Developer version so that we can have Analysis Services along with the regular relational database functionality. If you don’t need Analysis Services and the cube functions, the Express version of Microsoft SQL will be fine.

Fig. 1: Downloading the Developer Version of Microsoft SQL Server

Run the installation executable and select the “Custom” installation type. The window will suggest a download location for the installation files, and you can choose a location different from the default if you wish.

Fig. 2: SQL Sever Download Media Location

The installation files for SQL Server 2019 require about 1.4 GB of hard drive space – when this is done the actual installation will open in the “SQL Server Installation Center”.

Fig. 3: SQL Server Installation Center

Click on the “Installation” link on the left side of the page. Select “New SQL Server stand-alone installation or add features to an existing installation”, which opens the SQL Server 2019 Setup window. Specify the Developer free edition and click on Next. Accept the license terms and click Next.

The installer will check for installation media and make some other checks – you may get a warning about the Windows firewall. If the ports aren’t opened correctly, we can fix this later – click Next. This brings us to the Feature Selection window. For our purposes, we only want the database engine for now so click that check box as shown in Figure 4.

Fig. 4: Install the Database Engine Features of SQL Server

We can come back later and install the Analysis Services. Note that there are some machine learning tools shown in Figure 1 – they won’t be supported after July 1, 2022, so don’t bother with them. You can always download and install the Anaconda distribution of Python and R for machine learning tasks.

When you’ve selected the components that you want to install hit Next. Select the checkbox to “Use Microsoft Update …” and hit Next. This will bring up the Instance Configuration window – the defaults are all fine (we are creating a Default instance, so we will connect to the server name “localhost” with the management software) – hit Next.

The default settings on the Server Configuration, service accounts, window are fine, hit Next.

In the Database Engine Configuration window, we make 2 changes:

  • On the Server Configuration tab, click the Add Current User to allow the current user to be the SQL Server administrator (check that Windows authentication mode is selected)
  • Fig. 5: SQL Server Configuration – Add Current User

  • On the Data Directories tab, change the path for data away from the C: drive if you have a second drive for Data. In my case I want the database data on the F: drive. Changing the base path in the “Data root directory” line will automatically change all the other lines as well.
  • Fig. 6: SQL Server Data Directories

Finally click Next to finish configuring the relational database.

This will bring us to the Ready to Install window which shows a summary of our database server configuration. Assume that everything is the way we want it and click on Install. The installation progress window appears with a green progress bar that we can watch as we wait. When the installation is done, we will see a confirmation window:

Fig. 7: SQL Server 2019 Installation Complete

If you don’t see this, you will need to uninstall and start again ☹.

At this point you have an SQL Server database instance running on your computer. Now we need to install the management software that will allow us to work with databases in that instance.

2) Installing SQL Server Management Studio (SSMS)

In addition to the SQL Server software, you will also need SQL Server Management Studio (SSMS), which can be obtained from this Microsoft download page.

This software connects to the database server and enables us to manage the server and the databases available on it. The current version of SQL Server Management Studio as of this writing is 18.12. Version 19 is in preview release and may be generally available when you read this.

Note that Azure Data Studio will be installed alongside SSMS and is also able to connect and work with the Microsoft SQL database server.

Download the installation executable to the location of your choice and run it by double clicking on the file. The filename for the version that I downloaded is “SSMS-Setup-ENU.exe”, and was about 690 MB in size. Click Install on the installation window when it appears – the default installation location is fine.

Fig. 8: Installing SQL Server Management Studio (SSMS)

When the installation is complete the installation window will show the message “Setup Completed – All specified components have been installed successfully.”, click the Close button.

Fig. 9:  SSMS Installation Complete

2.1) Testing Your Connection to SQL Server with SSMS

To run SSMS, click on the Start button and find the group “Microsoft SQL Server Tools 18”, and click on “Microsoft SQL Server Management Studio 18”. You will need to connect to your SQL Server instance. The SSMS connection dialog will look like Figure 10.

Fig. 10: Connect SSMS to SQL Server

We are connecting to a database engine, so select that from the top dropdown. We installed a default instance of the database server, so we connect to the generic “localhost”. Note that we could also use the computer’s name as the server name. Finally, I selected Windows Authentication when the server was installed so select that for authentication. Click on Connect and you will see some high-level details about the SQL server.

Fig. 11: The Default SQL Server Instance

Expand the Databases folder then the System Databases subfolder to see the default databases that come with SQL Server as shown in Figure 12.

Fig. 12: System Databases

These won’t be of any interest to us for data exploration and business intelligence, but we have verified that the first 2 pieces are correctly installed.

3) Restoring the AdventureWorks Database

The AdventureWorks database for Business Intelligence can be downloaded from one of the links below:

Select your language and click the Download link. There are three versions available for download as well as scripts to set up an OLAP cube based on the dataset:

  • OLTP (Online Transaction Processing) – the dataset configured for our needs
  • Lightweight – a smaller version of the OLTP data set
  • Data Warehouse (DW) – a data warehouse created from the OLTP database
  • AdventureWorks for Analysis Services – create an OLAP cube from the Data Warehouse DB

The downloadable files have the extension “.bak” indicating they are database backup files for SQL Server. These database backup files are required to restore the dataset to SQL Server. We are now only interested in the contents of “AdventureWorks2019.bak”. Save the “.bak” file to a directory that you can find.

My installation has a database backup directory on the F: drive at

F:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup

Note that this directory was changed from the default location on the C: drive during the installation of SQL Server described above.

The SQL Server Backup directory is protected from ordinary users and we want to put the file “AdventureWorks2019.bak” into that backup directory. On Windows 10 I was able to provide Administrator authorization and simply click into the folder with File Explorer. From there, I could drag the file from the download folder to the MSSQL Backup folder – copy and paste work as well, of course.

Windows 11 can be a bit fussier, but we can copy the .bak file to the Backup directory using a Command Prompt running as Administrator. Go to the Start menu and search for “Command Prompt” – right-click on the app and select “Run as administrator” as in Figure 13.

Fig. 13: Run the Command Prompt as Administrator

Figure 14 shows the sequence of commands to copy the database backup file to the SQL Server backup directory. Switch to the drive with the database backup folder – in my case it’s F:, so I type “F:” (without the quotes) and hit Enter. Now change the directory to the SQL Server backup directory with:

cd “F:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup”

Note that here you need the quotes since there are spaces in the path.

Now use the “copy” command to make a copy of the database backup in the current working directory:

copy F:\Data\AdventureWorks\AdventureWorks2019.bak .

Note that the period at the end of the line indicates that the file should be copied to the current working directory.

Use the dir command to verify that the backup file in now where we want it.

Fig. 14: Use the Windows Command Prompt to Move the Database Backup File

In summary, I used a Command Prompt running as an Administrator to copy the AdventureWorks backup file “AdventureWorks2019.bak” to the location of the database backup directory on my computer:

“F:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup”

This should work if your account does not have sufficient permissions to open the Backup directory with File Explorer. The purpose for copying the database backup here is that this is the first place that SSMS will look for a database backup, so finding it to restore is easy.

We don’t need the Command Prompt anymore, so either click the X in the upper right corner, or type “exit” to close the window.

We will now use SQL Server Management Studio (SSMS) to restore the AdventureWorks database from the backup file. First open SSMS and connect to SQL Server as described in Section 2.1.

To restore the backup, right-click on the Databases folder in the Object Explorer, and select Restore Database as shown in Figure 15.

Fig. 15: Restore Database in SQL Server

This brings up the Restore Database dialog shown in Figure 16. Click the radio button for Device and the ellipsis to the right on the same line.

Fig. 16: Choose the Type of Data Source to Restore From

We can now set the Backup media type as File as in Figure 17, and then click Add to browse for and choose the backup file.

Fig. 17: Restoring a Database From a File

Since the file is in the Backup folder, we see it listed in the right pane of Figure 18.

Fig. 18: The SQL Server Backup Folder Contains the Backup File

Click on the file to select it and then click OK. Click OK several times to close the dialog boxes. When the database restoration is ready you will see the window in Figure 19.

Fig. 19: Ready to Restore The Database

Click OK to start the actual restoration of the database – this could take a couple of minutes, depending on your computer’s configuration. When the database restoration is completed successfully, you will see the popup as shown in Figure 20 – click OK.

Fig. 20: Database Restored

4) Working with the Restored Database

Before we start writing queries against the database, we need to understand its structure. We can examine the structure of the database by using the diagram drawing tool in SQL Server Management Studio. Connect to the database server instance with SSMS and expand the Databases folder and then the AdventureWorks2019 folder. Right click on the Database Diagrams folder and select New Database Diagram.

Fig. 21: New Database Diagram for AdventureWorks

This gives me the message in Figure 22 letting me know that I don’t have the required permissions to create a database diagram.

Fig. 22: SSMS Error – “dbo” does not exist

This can be fixed by setting myself as a new database administrator. Right-click the AdventureWorks database folder and click on Properties.

Fig. 23: AdventureWorks Database Properties

Click Files on the left side of the Database Properties panel and note that there is no owner assigned to the database. To make yourself the owner, click the ellipsis on the right side of the owner field to open the Select Database Owner dialog.

Fig. 24: AdventureWorks – No Database Owner

Click the Browse button in Figure 25 to select the user that is to be the database owner. In this case, you want to select your own account.

Fig. 25: Browse for New Database Owner

The name of my computer is ODIN and my username is jonj so after clicking on Browse… I see the list of users shown in Figure 26.

Fig. 26: Select Your Computer Account to be the Database Owner

Select the checkbox to the left of your username and click OK, and then click OK again to set the database owner. Click OK one last time to close the dialog boxes. Now that you are the database owner you are able to create diagrams and run queries against the database.

5)     Test That the Database Diagram Works

Having set the database owner, we should now be able to create a database diagram. Open the SQL Server Management Studio and connect to your server instance as in Section 2.1. Expand the Databases folder in the SSMS Object Explorer and expand the AdventureWorks2019 folder. Right click on New Database Diagram. The first time you do this you will probably get a warning about installing objects for database diagramming:

Fig. 27: Create Database Diagramming Support Objects

Click on Yes to generate the components.

After all of this we can click on New Database Diagram and create an actual diagram of tables and their relationships.

Fig. 28: New Database Diagram

Selecting New Database Diagram brings up the Add Table dialog shown in Figure 29, which lists all the tables available in the database. Click on the first table that you want to include in the diagram and then hold the CTRL key down while left-clicking on the other tables that you want to include. For this example, we will create a diagram of the Person schema of the AdventureWorks database – this has 13 tables where the names all end with (Person).

Fig. 29: CTRL-Click to Select Tables (only 6 out of the 13 are shown)

When the desired tables are selected – there are 13 (Person) tables, click the Add button to add the tables to the diagram. The Add Table dialog will remain open so click on Close to see the diagram. If the tables are too large to see on the screen at once, you can right-click on the background of the diagram and select Zoom -> To Fit to have the full diagram resized to the canvas of your monitor.

Fig. 30: Zoom the Diagram To Fit the Window

The resulting diagram shows all 13 tables and the relationships between those tables.

Fig. 31: Diagram of the AdventureWorks Schema for Person

Now that we have the tools to understand the structure of the database, we are ready to extract the data and provide some analysis beginning with some simple SQL queries. That is a topic for another post …

In this post we have:

  • installed SQL Server,
  • installed Microsoft SQL Server Management Studio,
  • restored the AdventureWorks database to our server instance,
  • verified that we have permissions to work with the database, and
  • created a schema diagram

This is a lot, it’s time to take a break and think about what we are going to do with all this. If you have the energy, there is a light version of the AdventureWorks database that has fewer tables. It can be restored through the same process described above, starting at Section 3 – just add it to the existing database instance and you can have both databases available for exploration in the same SQL Server instance.