As tends to happen this isn’t the post I set out to write today. We hit an issue at work the other day that I want to write about, but to do so I need a sample database (I can’t use the data from work). I wanted something that is more fleshed out that my normal single table contact database. I google for AdventureWorks, since that is the sample database I have always seen in examples.
Options
Turns out that AdventureWorks isn’t the only SQL Server sample database option in town these days. Microsoft has a SQL Server Samples repo that has three different options depending on your needs. The following is a description and links to all there options as they stand right now.
The new sample database for SQL Server 2016 and Azure SQL Database. It illustrates the core capabilities of SQL Server 2016 and Azure SQL Database, for transaction processing (OLTP), data warehousing and analytics (OLAP) workloads, as well as hybrid transaction and analytics processing (HTAP) workloads.
Sample data warehouse that illustrates loading data into Azure SQL Data Warehouse.
Sample databases and Analysis Services models for use with SQL Server.
Since I’m not dealing with a data warehouse the middle option is out. AdventureWorks is still a valid option, but I just can’t pass up WideWorldImporters which is the newest sample used to show off SQL Server 2016.
Getting Started
Make sure you have both SQL Server and SQL Server Management Studio installed. For SQL Server we will be using the on-premises version. I recommend grabbing the developer edition as it provides the full set of features for free as long as it isn’t used in production. Once you have both of the above installed it is time to get download the database related files.
As of this writing, this GitHub release page contains the latest bits to get started with, and yes it is from June of 2016. There are a lot of options listed, but the file we are interested in is WideWorldImporters-Full.bak. If you are looking to explore some of the new features of SQL Server 2016 you should also grab sample-scripts.zip to play with, but this isn’t going to be covered in this post.
Restoring the Database
Now that we have the backup of the database we are going to restore it is time to open up SQL Server Management Studio. In the connection dialog, connect to the server you want the database to end up on. In this case, I’m connecting to a SQL Server instance running on my local PC. When you are all set hit the Connect button.
Once connected you should see the selected server in the Object Explorer window.
Right-click on the Databases folder (or node if you prefer) and click Restore Database.
This will launch the Restore Database dialog. This dialog is full of stuff, but we are going to focus on the minimum needed to restore a database from a backup file. First, select the Device option and then click the … button.
This will show the Select backup devices dialog. We want to use the File type and then click the Add button.
On the next screen, you need to enter the path to your backup file. I’m not sure why, but this isn’t the easiest dialog for finding a file. I found it easier to use Windows Explorer to find the directory the backup file is in and copy it to the Backup File Location. Once you have the right directory select the backup file and click OK.
Back on the Select backup devices dialog click the OK button to continue. This will land you back on the Restore Database dialog which will now display information from the backup that was selected. Click the OK button to start the restore process.
After a minute or so the restore process should complete. If you expand the Databases node back in the Object Explorer window you should see the restored database listed.
Data Generation
The WideWorldImporters database comes with a stored procedure that will generate current data for you to work with. This isn’t a fast process so be prepared to wait if you decide to run this. To start open a new query window for the WideWorldImporters database by right-clicking and selecting New Query.
If you run the following query it will start the data generation process.
EXECUTE DataLoadSimulation.PopulateDataToCurrentDate
@AverageNumberOfCustomerOrdersPerDay = 60,
@SaturdayPercentageOfNormalWorkDay = 50,
@SundayPercentageOfNormalWorkDay = 0,
@IsSilentMode = 1,
@AreDatesPrinted = 1;
The official docs have more details on data generation. From the docs, it says that data generation will take about 10 minutes per year and starts off from 2016 so you are looking at a 30-minute minimum runtime (and based on my test that 10 minutes per year number is much too low). Do note that the back up comes with data so this process is only needed if you want recent (date wise) data.
Wrapping Up
The above process wasn’t hard, but if you are like me and haven’t worked much with database backup hopefully you found this post helpful. I’m sure you will find the sample database helpful for trying out some of the newer features that SQL Server or for a source of data that is OK to use publically.
Make sure you check out the official docs pages for Wide World Importers. It has a lot more information as well as instructions for other workload use cases such as data warehousing.
Also published on Medium.
Thanks, Eric. You just gave me some additional resources to understand more about data warehousing! Thanks!
I’ll be using the AdventureWorks OLTP database for my talk at the Nashville .NET User Group [ https://www.meetup.com/Nashville-NET-User-Group/ ] next month. The data generation stored procedures will expedite the process for generating demo data!
Pingback: SQL Screw-ups – Evan Smith
Pingback: Database Migrations with DbUp – Eric L. Anderson