Installing Adventure Works

Welcome to my first in a series of posts in response to Tim Ford's #EntryLevel Challenge, which I learned about indirectly from reading a post by Steve Hood. SQL is what I'm good at, so I will be focusing on SQL and T-SQL in this series. 


To learn or practice with SQL requires two things: A database engine, and some example data. Microsoft SQL Server Express is a freely available and easy-to-install engine. Microsoft's Adventure Works example database provides a good set of tables with data designed to show off all that SQL is capable of doing. Put SQL Server Express together with Adventure Works, and you have a nice platform on which to learn and practice the SQL language.

Installing SQL Server Express

SQL Server Express 2014 is the current version of Microsoft's free database engine. There are five editions of Express, and you can learn about them on the MSDN download page in Figure 1:

https://msdn.microsoft.com/en-us/sqlserver2014express

The edition I recommend is termed as "Express with Tools". Begin the download process from the page in Figure 1. You'll need to fill out the form in Figure 2. You'll also need a Microsoft Live account.

Figure 1. Choose "Express with Tools" 

Figure 2. Fill out the form

Download the installer. Run it. Take all the defaults, and just click Next, Next, Next in the usual manner. The defaults are suitable for a learning and practice environment. 

When the install is over, find and run the program named SQL Server 2014 Management Studio. Search on "Management Studio" from the Start menu, and you should find it.

Login as shown in Figure 3. Select the SQLEXPRESS server name from the dropdown menu in the dialog. Management Studio will open. Click the plus sign (+) to drill into your databases. You should see just the System databases as shown in Figure 4.

Figure 3. Logging in the first time

Figure 4. There's no example data yet

There's no adventure yet. But we're working on it. Installing Adventure Works is next. 

Installing Adventure Works

Microsoft makes the Adventure Works database available from their SQL Server Product Examples page at msftdbprodsamples.codeplex.com. You can download a set of scripts to create the database, but I prefer to grab the full backup and restore from that.

Choose the Adventure Works year as shown in Figure 5. I chose 2014 to go along my 2014 install of Express. Then choose the option to download a database backup as in Figure 6. Finally, it's worth grabbing the Readme file shown later in Figure 7. The readme is at the very bottom of the download page.

Figure 5. Choose the year of Adventure Works

Figure 6. Choose the database backup

Here is where things get a tad tricky. You want to unzip the downloaded archive and copy the .BAK file that's within it to the Backup folder under your SQL Server Express install. Figure 8 shows the Backup folder and its location on my own system. 

Be aware that the Readme file from Figure 7 provides the path for the non-Express edition of SQL Server. Your path will be somewhat different by descending through MSSQL12.SQLEXPRESS. 

Figure 7. Grab the Readme file

Figure 8. Put the .BAK file into your Backup folder

 

The reason for moving the .BAK file is to avoid permissions issues during the restore. SQL Server has access to its own folders, but not to other folders on your system outside the install. So copy the .BAK file to your Backup folder, and save yourself the trouble of sorting out a file access error during the restore operation. 

Note: You might receive a permissions error when navigating to your Backup folder in order to make the copy. Just click Continue when that happens to give yourself access to the folder. 

Now run a RESTORE DATABASE statement, and it's "job done". Following is a script showing the statement that I ran on my own system. Find the command in Figure 7's Readme file. It'll be in the section on restoring from a backup. Then modify the paths to be correct for your system.

USE [master]

RESTORE DATABASE AdventureWorks2014
FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_data' 
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.mdf',
MOVE 'AdventureWorks2014_Log' 
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.ldf'
,REPLACE

Be sure the FROM path points to your .BAK file. The other two paths should point to your DATA directory underneath your SQL Server Express install. 

The change you are most likely to need to make—if you copy and paste from the Readme file in Figure 7—is to change "MSSQL12.MSSQLSERVER" to "MSSQL12.SQLEXPRESS" in all three paths. That's the one change I made before executing the statement.

Now it's time to execute the RESTORE DATABASE statement. Click Databases in the tree at the left pane of the management Studio window. Then click the New Query toolbar button to open a query window. Paste the USE and RESTORE DATABASES commands into the query box as shown in Figure 9, and press the Execute button in the toolbar to create the example Adventure Works database shown in Figure 10. 

Figure 9. Restoring from the backup

Figure 10. Adventure Works is available

The Adventure Works database will be restored from the .BAK file and made available in your environment. Right-click Databases in the left pane. Select Refresh. You should see the Adventure Works database. Expand that entry and you can drill down to see what tables, columns, indexes, views, and other structures are available. 

Learning and Practicing

Congratulations! You've just installed one of the best environments for learning about the SQL language and how it can be used to query and analyze data. It's an environment shared by some of the best and most helpful database professionals whom it is my pleasure to know. Look for more posts from me this year on SQL.

And get to know the community! A good way to do that is by attending one of the many SQL Saturday events held locally around the planet. 

Welcome! To SQL Server.