A lot of times when you're learning how to code, a lot of things seem relatively obtuse or inaccessible when you're trying to set up your environment. You spend a lot of time on Stack Overflow, or Google, just wading through the countless posts, guides, or other content. Where do I download this? How do I install that? What setting do I need to change and how do I get to them?

Probably the biggest need (for quite a few of us) is having a database for practice. The good thing is, there are options out there for installing a database, LOCALLY, right on your personal computer.

What about getting a fully featured database, with data? Another task all in and of itself. That's where this guide comes in. I'm going to walk you through how to install Microsoft SQL Server, locally, and initialize a fully featured database that you can use for practice or development.

Before we get started, this guide is for Windows. Mac or Linux users, you can still get SQL installed locally, but it's likely going to be a bit more involved process.

Downloads

You'll need a few downloads for this guide, I'll go through each below.

Microsoft SQL Server

Microsoft SQL Server is Microsoft's standard relational database. It's been around for quite a while, so it has a LOT of different features. The full version costs money. However, Microsoft makes their developer edition available for FREE. The best part of the developer edition? It's a fully featured, full edition. The only caveat is you are not allowed to use it for anything production*.

Go here and scroll down, then use the link to download the developer edition.

As well, before proceeding ensure you meet the system requirements.

*If you need a free production version, you can install SQLExpress. It's limited in storage size and features, but it's enough to get you started.

SQL Server Management Studio (SSMS)

This is Microsoft's fully featured software for managing Microsoft SQL Server and writing SQL scripts. It has a tremendous amount of features, and it's available for free. One of the best things about it is it gives you options to manage the database without a whole lot of code, which is a HUGE timesaver if you're just starting out. The only caveat is it only works with Microsoft SQL Server.

There are alternatives to SSMS that work with other databases (should you have other databases you plan on working with), however, these are going to be more code-based and a little more technically involved. I will provide some alternatives at the bottom of this post.

Go here and use the link to download SSMS.

As well, before proceeding ensure you meet the system requirements.

AdventureWorks Database

AdventureWorks is a full-scale database Microsoft provides for practice (particularly the OLTP version). It has schemas, plenty of tables, stored procedures, and triggers. So, if you're after a fully featured database for practice, it's a great option instead of creating one on your own.

Go here and use the link to download the AdventureWorks bak file. Download the OLTP version. As of writing, the most recent is the 2019 one.

Installing SQL Server and SSMS

Once you've downloaded both SQL Server and SSMS, go through the installations like you would any other Windows program; double-click the installer, and proceed through the installation.

For SQL Server, ensure you choose the "Basic" installation type.

You can install these in any order, but ensure they both are successfully installed before proceeding to the next step.

Connecting To SQL Server

Once you have both SQL Server and SSMS installed run SSMS. You can likely just search for SSMS in your applications and it should show up.

When you first launch SSMS, it should present an option to connect. It will try to connect to your local machine (localhost or PC name). It should look something like this:

If you've installed SQL Server, you should be able to just hit connect. If it successfully connects, it should look something like this (except it will be your PC name versus "WIN10-VM"):

If it fails to connect, or it doesn't present connection information like the picture above, you can try to connect to "localhost" - localhost is just shorthand for PC you're working on:

If it still fails to connect, ensure that everything has been installed correctly. Otherwise, you will likely have to troubleshoot issues with the server; which recommendations will vary depending on what error you're getting.

Initializing AdventureWorks

Once you've connected, download the AdventureWorks.bak file. Once downloaded, we're going to restore the database to our SQL server. However, I recommended moving the .bak file to your c:\ drive; as it will be easier to access the file.

Point and Click Method

Within SSMS, once connected to the server, right-click "Databases". Under the selection, choose "Restore Database...":

A dialogue window will appear, go through the following steps:

1. Choose "Device"

2. Click the ellipses "..."

3. Click "Add"

4. Navigate to the .bak file, select it

5. Click "Ok" back through the dialogue windows, until you're back at the "Restore Database" dialogue window

Once you're back to the "Restore Database" window, everything should read from the .bak file, and the "Database" field will populate. I'd recommend changing this to just AdventureWorks. However, once all that is done, you can just hit "Ok".

Once done, you should just get a "successful" window:

Code Based Approach

You can also take the code-based approach to restore AdventureWorks. I will note this method will likely require some small modifications, so if you're not super comfortable with SQL, I highly recommend you use the "Point and Click" method above.

To restore a database in SQL server, you have to use the "restore database" command. Below is the command. The only things you should have to change would be the location of the .bak file (the "disk" option), and the location you would like to store the database files (both of the "move" options).

use master
go
restore database [AdventureWorks] --This is the name of the database
from disk = N'C:\AdventureWorks2019.bak' --This is the location of the .bak file
with file = 1
    , replace
    , norecovery
    , nounload
    , stats = 5
    --These are the location of the database files - you may have to adjust this path depending on where you've installed SQL, or where you want to store your files
    , move 'AdventureWorks2017' to 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks.mdf' 
    , move 'AdventureWorks2017_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks_log.ldf'
go

Once Restored

Once everything is restored, just to ensure everything looks good, you should see "AdventureWorks" in your object explorer within SSMS; as well, I just recommend creating a "New Query" and executing something:

select top 100 *
from adventureworks.person.Address

If a result returns, congratulations! You restored a database! Now you have a fully featured database to play around with.

If you don't see the database, try refreshing or reconnecting via SSMS.

Conclusion

Installing a local SQL server will provide tremendous value for any project you may be working on, whether it's learning SQL, building an application, or any other project where you may need a database.

I hope you found this helpful!

Other SQL Editors

Azure Data Studio

Azure Data Studio is Microsoft's other SQL editor - it's very similar to Visual Studio Code (if you've used VSCode before), and also has support for other databases besides Microsoft SQL Server.

For restoring databases and performing other database management tasks, Azure Data Studio has some point-and-click features so you don't have to do everything in a code-based approach. It's also available for Windows, Mac, and Linux.

You can download it here.

DBeaver

DBeaver is a full-featured SQL editor that supports a LOT of databases. It's mostly code-based, however, it has some other nifty features built in. If you're looking for a pretty full-featured, flexible, and free SQL tool, I highly recommend it. It also has a paid version with additional features and connectivity.

You can download the free community edition here.