How to Restore a Database in SQL Server

Restoring a database in SQL Server is a core DBA task.

Whether you’re responding to an incident, performing disaster recovery testing, migrating data, or rebuilding an environment, restores need to be predictable, repeatable, and verifiable.

This post walks through restoring a database using both T-SQL and SQL Server Management Studio, along with the checks that matter before and after the restore completes.

---

When You Typically Restore a Database

You’ll usually restore a database when:

• Recovering from data loss or corruption
• Rebuilding or refreshing environments
• Migrating databases to new hardware or storage
• Configuring features like Always On or mirroring
• Validating backups as part of DR testing

The mechanics are straightforward. Problems usually come from skipping validation or clean-up steps.

---

Restore a Database Using T-SQL

For repeatable work, automation, or multi-database restores, T-SQL is usually the safest option.

Many DBAs use the SSMS wizard to generate a script, then adjust and run it manually.

Example: Restore a Database from a Full Backup

USE master;
GO

RESTORE DATABASE [lemonadestand]
FROM DISK = N'C:\temp\lemonadestand_full_11082022.bak';

If the backup is valid and no additional restore steps are required, SQL Server will bring the database online automatically.

---

Restoring a Database from Multiple Backup Files

Large databases are often backed up using multiple files to improve throughput or manage storage.

When restoring, list all backup files in the same RESTORE command.

RESTORE DATABASE [lemonadestand]
FROM DISK = N'C:\temp\lemonadestand_full_part1.bak',
     DISK = N'C:\temp\lemonadestand_full_part2.bak';

SQL Server reads from all files and restores the database as a single operation.

---

Restore a Database Using SSMS

The SSMS restore wizard is useful when you need visibility, file relocation, or quick validation.

Step 1: Open the Restore Wizard

Right-click Databases in Object Explorer and select Restore Database.

Step 2: Select the Backup File

Choose Device as the source, browse to the backup location, and select the .bak file.
Multiple files can be selected if required.

Step 3: Review and Relocate Data and Log Files

Before running the restore, always check the Files tab.

This shows where SQL Server will place the data and log files. Adjust paths if:

• Restoring to a different server
• Storage layouts differ
• You want to avoid default directories

Options Tab (What Actually Matters)

You usually only need to check a few things here:

• Overwrite the existing database
  Required when restoring over an existing database.
• Preserve replication settings
  Only relevant if replication is configured.
• Recovery state
  Leave as WITH RECOVERY unless you are restoring additional files or logs.

Everything else is rarely needed for standard restores and can usually be left at default values.

---

Verify the Restore Completed Successfully

After any restore, always verify the database state.

SELECT name, state_desc
FROM sys.databases
WHERE name = 'lemonadestand';

You are looking for:

• ONLINE state
• Expected compatibility level
• Correct owner and configuration

If the database is not online, stop and investigate before proceeding.

---

Common Restore-Related Checks

Restores rarely happen alone. These checks often come next:

👉 Get Last Database Restore Date and Time in SQL Server
Confirm when a database was last restored.

👉 Estimate Backup and Restore Completion Time in SQL Server
Track progress for long-running restores.

👉 Database Cannot Be Opened – It Is in the Middle of a Restore
Resolve databases stuck in restoring state.

👉 Why Is the Database in Recovery Mode
Troubleshoot databases that appear online but remain unusable.

👉 Kill All User Sessions on a Database in SQL Server
Clear blocking connections before or after restores.

---

Final Thoughts

Restoring a database should never feel rushed.

When you combine:

• Scripted or controlled restores
• File location verification
• Post-restore validation
• Supporting checks for progress and history

you remove most of the risk from recovery work.

This post, together with the related restore and backup checks, forms a solid, practical baseline for real-world SQL Server restore operations.