Restore Operations

The RESTORE statement recovers a database from a backup set. This article provides guidance on using RESTORE, the options available, and best practices for safe restoration.

Table of Contents

Basic Restore

To restore a full database backup:

RESTORE DATABASE AdventureWorks
FROM DISK = N'C:\Backups\AdventureWorks_Full.bak'
WITH REPLACE, RECOVERY;

Common Options

OptionDescription
WITH REPLACEOverwrites an existing database.
WITH RECOVERYBrings the database online after restore.
WITH NORECOVERYLeaves the database in restoring state for subsequent restores.
WITH MOVERedirects data and log files to new locations.
FILERestores a specific file or filegroup.
STANDBYLeaves the database read‑only, allowing further restores.

Point‑in‑Time Restore

Restore to a specific moment using a transaction log backup chain:

RESTORE DATABASE SalesDB
FROM DISK = N'C:\Backups\SalesDB_Full.bak' WITH NORECOVERY;
RESTORE LOG SalesDB
FROM DISK = N'C:\Backups\SalesDB_Log1.trn' WITH NORECOVERY;
RESTORE LOG SalesDB
FROM DISK = N'C:\Backups\SalesDB_Log2.trn'
WITH STOPAT = '2024-03-15T14:23:00', RECOVERY;

Partial Restore (Files and Filegroups)

Restore only selected files or filegroups. Useful for large databases with read‑only filegroups.

RESTORE DATABASE LargeDB
FILE = N'Primary', N'FG_ReadOnly1'
FROM DISK = N'C:\Backups\LargeDB_Partial.bak'
WITH REPLACE, RECOVERY;

Restoring Metadata Only

When you need only the schema (no data), use WITH NORECOVERY followed by RESTORE DATABASE ... WITH REPLACE, NORECOVERY and then RESTORE LOG ... WITH NORECOVERY before a final RESTORE DATABASE ... WITH RECOVERY where you specify DATAFILE = N'NULL' (SQL Server 2022+).

Additional Examples

Restore with MOVE

RESTORE DATABASE TestDB
FROM DISK = N'D:\Backups\TestDB_Full.bak'
WITH MOVE N'TestDB_Data' TO N'E:\Data\TestDB.mdf',
     MOVE N'TestDB_Log'  TO N'E:\Logs\TestDB.ldf',
     REPLACE, RECOVERY;

Restore to a Standby State (Read‑Only)

RESTORE DATABASE ReportingDB
FROM DISK = N'C:\Backups\ReportingDB_Full.bak'
WITH STANDBY = N'C:\Backups\ReportingDB_undo.ldf';

For more details on each option, see the Backup & Restore Documentation.