In this series, we will examine the steps necessary for creating, managing, and querying your database(s) in Microsoft SQL Server. It is assumed that you have already downloaded a recent SQL Server edition (such as Microsoft SQL Server Express 2012 or newer) and have acquired the relevant data files if you will be using the sample data provided by Microsoft.
In SQL Server editions prior to SQL Server 2016, the popular "Adventure Works" database and data warehouse datasets were used. With the release of SQL Server 2016, Microsoft is deprecating its support for "Adventure Works" and has released a new dataset based on a new, fictitious company called "Wide World Importers". You can find a link to the Microsoft blog article here.
Thus, we will be using the new dataset for this series. So let's begin.
First, you could create a new, blank database simply by using SQL Server Management Studio (SSMS), right-clicking the "Databases" tree and selecting "New Database..." and then following the prompts.
This is a novice way of creating an empty database, and we must emphasize that it is an absolute skeleton of one. There are no tables and thus no data to work with. You could perform ETL processes (extract transform and load) through applications such as SQL Server Integration Services (SSIS) for bulk loading of data into new tables from existing, compatible data sources such as flat files, but we won't touch on that at this time. Instead, let's look at another way of creating a database: from a backup.
You will be doing this anyway if you plan to use Wide World Importers (or even the legacy Adventure Works) database for your sandbox.
Note: you can also use the steps below in a similar fashion to restore an existing database from a backup.
So let's get right to it and into the step-by-step process:
Open up SSMS, log in, right-click on the "Databases" tree and click on "Restore Database..."
Select the source location of the database you wish to restore. In this case it will be on your device in a logical location on your computer where the .BAK file is stored for the database.
Once you have found and selected the backup database file, it will show up in the list and you can click "OK" to continue.
The image below shows you roughly what you should see at this point in the process of restoring the database onto your MSSQL server.
Once you click "OK" again, you'll be greeted with the progress wheel at the bottom-right corner of the window. Sit back and relax. For many enterprise-level databases, this can take a considerable amount of time. Thankfully, the "Adventure Works" and "Wide World Importers" sample DBs are rather small so this process is quick.
So long as everything went well and no critical errors occurred, you will be greeted with a new database entry in the object explorer of SSMS. You can expand the tree to view its contents, including tables, views, et cetera.
Congratulations! You have just restored a database from a backup or, in this case, created a brand new database from a backup copy from a Microsoft sample. We will be using this database going forward to explore the newest functionality of SQL Server Express 2017 and subsequent versions that will inevitably be released.