SQL Server Management Studio

Create the course's sample databases in SSMS, find your way around Object Explorer, and run your first queries.

Novice · Database · T-SQL · Updated

After this lesson you can

  • Create a sample database in SSMS by running a script or restoring a backup
  • Browse a database's tables in Object Explorer and run a query against it

Key takeaways

  • SQL Server stores the data; SSMS stores nothing itself. It's the window where you look at the data and send SQL to the server.
  • Running a .sql script sends every statement in it to the server. Running Northwind.sql or BooksPlus.sql again recreates that database with its original data.
  • A backup (.bak) is a whole database in one file, and SQL Server restores it only from a folder it can read, such as its Backup folder.
  • In Object Explorer, expand a database's Tables folder to see its tables and columns, and use Select Top 1000 Rows to see a table's data.
  • A new query window runs against master until you pick a database in the toolbar drop-down or add USE Northwind;. F5 runs the window, or just the lines you select.

SQL Server is the database engine you installed during setup. It runs in the background and stores your data. SQL Server Management Studio (SSMS) is the app you use to look at that data and to send SQL to the server. SSMS doesn't store anything itself; it's a window onto the server.

In this lesson you'll create the three sample databases the course uses, and run your first queries against them.

Connect

Open SQL Server Management Studio 22 and connect to your server, as you did in setup:

Field Value
Server name localhost
Authentication Windows Authentication
Trust server certificate Checked

Object Explorer, on the left, shows your server as a tree. Expand Databases. On a new server there are no databases of your own yet, only a System Databases folder that SQL Server uses itself.

SSMS with Object Explorer on the left, showing localhost and its Databases folder

Get the sample database scripts

Open this lesson's Exercise code link to go to the course's sample databases on GitHub. Click Code, then Download ZIP, and unzip it into your bootcamp folder.

Database What it holds
Northwind A small food-trading company: customers, orders, products, suppliers and employees
BooksPlus A chain of bookstores: books, authors, publishers, stores, customers and orders
AdventureWorksLT A bicycle company: products, categories, customers and sales orders

A script is a text file of SQL, with a .sql extension. Running a script sends every statement in it to the server, one after another.

Create Northwind and BooksPlus

  1. In SSMS, choose File > Open > File... (or press Ctrl+O), and open Northwind.sql from the folder you unzipped.
  2. The script opens in a query window. Press F5 (or click Execute).
  3. The Messages tab below fills with lines as the script runs. When it finishes, it ends with Completion time.
  4. In Object Explorer, right-click Databases and choose Refresh. Northwind appears.

Do the same with BooksPlus.sql.

Running either script again deletes that database and creates it fresh, with its original data. If you ever change data by mistake, that's your undo button.

Restore AdventureWorksLT

AdventureWorksLT comes as a backup: a single file holding a whole database. Download it from Microsoft:

https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksLT2022.bak

SQL Server can only read backups from folders it has permission for, so:

  1. In File Explorer, copy AdventureWorksLT2022.bak into C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup. Click Continue when Windows asks for permission.
  2. In SSMS, right-click Databases and choose Restore Database....
  3. Under Source, choose Device, click the ... button, then Add. Pick AdventureWorksLT2022.bak and click OK, then OK again.
  4. Click OK to restore. When it says the restore was successful, refresh Databases: AdventureWorksLT2022 is there.

Look around a database

Expand Northwind, then Tables. Each table has a name that starts with dbo., such as dbo.Customers; dbo is the table's schema, a group the table belongs to.

  • Expand a table, then its Columns folder, to see each column and its type.
  • Right-click dbo.Customers and choose Select Top 1000 Rows. SSMS writes a query for you, runs it, and shows the rows in a grid.

The results grid showing rows from the Customers table

Spend a few minutes looking through the tables. As a developer who works with data, you need to know what's stored, and how the tables relate to each other, before you can build anything useful with it.

Run your own query

  1. Click New Query on the toolbar. A blank query window opens.
  2. Choose which database it runs against: pick Northwind in the database drop-down on the toolbar (it says master at first).
  3. Type a query, and press F5:
SELECT CompanyName, City, Country
FROM Customers;

The Results tab shows 91 rows: every customer's company name, city and country. You'll learn exactly how this query works in the next chapter.

To run only part of a window, select those lines first, then press F5: SSMS runs just the selection.

Common problems

What you see What to do
Invalid object name 'Customers' The query ran against the wrong database. Choose Northwind in the toolbar's drop-down, or add USE Northwind; above the query.
A new database doesn't show in Object Explorer Right-click Databases and choose Refresh.
The restore says it can't open the backup file, or access is denied The .bak file isn't in SQL Server's Backup folder. Copy it there and try again.
Cannot drop database because it is currently in use Another window is using that database. Close the other query windows, or switch their drop-down to master, then run the script again.

Check yourself

2 questions to try on the lesson page.

So what

Northwind and BooksPlus are the data you'll query, change and build apps on for the rest of the course. AdventureWorksLT is a bigger database to explore on your own, and SSMS is where you'll try every query before it goes into your C# code.

Key terms

  • SQL Server
  • SQL Server Management Studio
  • Script
  • Backup and Restore
  • dbo Schema

Cheat sheets