How to Restore a Database in SQL Server

Restoring a database in SQL Server is an essential task for any Database Administrator. Whether it’s part of a disaster recovery plan, setting up a test environment, or moving data between servers, you’ll likely restore databases countless times in your career.

You can restore databases through the SQL Server Management Studio (SSMS) graphical interface or by executing commands via T-SQL. While SSMS is user-friendly, scripting the restore command is often preferred by experienced DBAs, especially when dealing with multiple databases or complex scenarios like restoring from multiple backup files.

In this walkthrough, we’ll cover the steps for restoring a database using T-SQL, including handling backups split into multiple files.
> Restoring a SQL Server Database using Command
> Restoring a SQL Server Database with Multiple Files

Restoring a SQL Server Database using Command

For this demonstration, I’ll work with a sample database [lemonadestand].

We’ll start with a full backup, drop the database, and then restore it using a T-SQL command.

1. Perform a Full Backup:
This is an example of a Single-File Database Backup.
First, back up the [lemonadestand] database to a single file:

-- Perform full database backup to local temp directory with compression
BACKUP DATABASE [lemonadestand] TO DISK = N'c:\temp\lemonadestand_full_11082022.bak' WITH COMPRESSION;

2. Drop the Database:
Simulate the need for a restore by dropping the database:

-- Drop the test database
DROP DATABASE [lemonadestand];

3. Restore the Database:
To bring the database back online, use the following command:

-- Restore test database we just dropped
RESTORE DATABASE [lemonadestand] FROM  DISK = N'C:\TEMP\lemonadestand_full_11082022.bak';

After executing the restore command, the database is fully operational again.

Restoring a Database from Multiple Files

In scenarios requiring improved backup performance or better disk space management, backups can be split across multiple files. Here’s how you can handle such cases.

1. Create a Split Backup:
Back up the [lemonadestand] database into two files:

-- Run Full database backup to local temp directory with compression
BACKUP DATABASE [lemonadestand] 
TO DISK = N'c:\temp\lemonadestand_full_11082022_part1.bak', 
   DISK = N'c:\temp\lemonadestand_full_11082022_part2' WITH COMPRESSION;

2. Drop the Database:
Again, drop the database to prepare for restoration:

-- Drop the lemonadestand database
DROP DATABASE [lemonadestand];

3. Restore from Multiple Files:
This is an example of a Multi-File Database Backup.
Restore the database by referencing both backup files:

-- Restore the lemonadestand database
RESTORE DATABASE [lemonadestand]
FROM DISK = N'C:\TEMP\lemonadestand_full_11082022_part1.bak', 
     DISK = N'c:\temp\lemonadestand_full_11082022_part2';

The database is now restored using multiple backup files and is ready for use.