Lecture 9-- Install a sample database
- [Instructor] Microsoft produces sample databases for a SQL server that are commonly used in training courses and tutorials that you're sure to come across in your journey towards becoming a database administrator. One of the most popular is a database called AdventureWorks, which follows a fictitious bicycle manufacturer. You can download and explore the AdventureWorks database by visiting Microsoft's GitHub repository at this URL. From here, simply go down, and click on the AdventureWorks2017.bak link. This database can be useful to have, since a large number of people have access to it and regularly refer to it in order to explain concepts or illustrate techniques.
The link will download a database backup file that will install in Management Studio in just a few minutes. The other sample database that Microsoft supplies is for a company called Wide World Importers. This is a new fictitious company that imports and distributes novelty gifts. This database is intended to replace AdventureWorks for future tutorials, so it's probably a good idea to grab that one, too. It also has a GitHub page downloaded, and you can find it at this URL. At the time of this recording, it's still in version one, so go ahead and just click on the WideWorldImporters-Full.bak file.
While it's downloading, you might want to review the link above for the complete documentation about the database. Once both database backups are downloaded, we need to add them to our instance of SQL server, and we'll use Management Studio to do that. Once we're logged into our server instance, we'll go over to the databases node, and right-click on it, and choose Restore Database. Then, underneath source, we need to change it from database to device. This will allow us to browse our hard drive. Now come over here to the far right and press the ellipsis button to choose a folder.
Our backup media type is going to be a file, but notice the other option is to reload it from a URL. We'll go ahead and leave it here on file, and then press the Add button. Note that SQL server has a default location that it expects backup files to reside in, and we can see its URL up here on the top. Let me go ahead and make this window just a little bit wider so we can see the full file path. On my computer, it's on the C drive, in the Program Files folder, in a folder for Microsoft SQL server, and then a folder that corresponds to my instance name. So we have the version of SQL server that we're using, MSSQL14, followed by a period, and then the instance name, which for me is MSSQLSERVER.
After that, we have another folder, called MSSQL, and finally, a folder inside of there called Backup. This is the default location that SQL server expects the backup files to reside in. SQL server also is restricted from gaining access to many locations on your computer that you might typically use, such as your user account's desktop, or downloads folder. So my recommendation is to simply use the folders that SQL server already expects. This means that we need to move the downloaded database backup files to the SQL server backup folder. So let's go ahead and back out of this by pressing the Cancel button a couple of times.
I'll go ahead and close this window, and go out to our desktop here, to move those files. I'll open up File Explorer, and go to my downloads folder. There we can see the two backup files that we just downloaded, here and there. I'll go ahead and press the Shift button, and select both of them at once. Then I'll right-click, and choose Cut. Then we need to browse out to our C drive, go to Program Files, go into Microsoft SQL server, the folder that corresponds to our instance name, the MSSQL folder, and then finally, the backup folder. It's going to warn me that I don't currently have permission to access the folder.
Go ahead and press the Continue button to gain access, and then once we're inside, we'll just right-click and choose Paste, to paste in those backup files. Now we can go ahead and close this, and return back to Management Studio. Once again, I'll go back to my databases node, I'll right-click on it, and choose Restore Database. I'll choose device again, and press the ellipsis button. Our backup media type is still a file, I'll press the Add button, and this time, we can see the two database backups. Let's go ahead and choose the AdventureWorks file first.
I'll select it and press OK, then press OK again, and then finally, at the very bottom, I'll press the OK button one more time. When it's done, the database AdventureWorks2017 is restored successfully. I'll say OK to that, and we should see it appear here in the databases folder. Now, if you're not seeing it, go ahead and right-click on databases, and choose Refresh. Next, let's load the other database. Once again, I'll right-click on databases, go into Restore Database, once again, I'll choose device, and the ellipsis button, I'll press the Add button here, and then choose WideWorldImporters-Full.bak.
I'll say OK a couple of times, and OK one more time. When it's done, we get the same restored successfully message, that's good. And we now have both AdventureWorks and Wide World Importers sample databases on our system. Now we can dig into the databases to explore some of the data they contain. Let's go ahead and go into Wide World Importers, expand the tables folder, and then I'll right-click on a table called Purchasing.PurchaseOrderLines.
From there, I'll choose Select Top 1000 Rows to see the data within. In the results that we see in the bottom section of the SQL query window, we can see that purchase order ID number one includes these first three records. The purchase order has three lines, and they're for a variety of action figures. When you're done exploring the data, go ahead and close the tab. So feel free to dig into some of the other tables to see the kinds of information that they store. Because these are the official example databases for Microsoft, you'll be all set to use them with tackling the tutorials found in the technical documentation.
▶️ DTube
▶️ IPFS