Skip to content
DBAExperts
SQL Server Backups & recovery

How to restore a backup in SQL Server (RESTORE DATABASE)

Restoring well is just as important as backing up. The key is understanding WITH NORECOVERY (to chain restores) and WITH RECOVERY (to bring the database online).

  1. 1

    Restore the full backup with NORECOVERY

    Use NORECOVERY if you plan to apply a differential or logs afterward. The database stays in the 'Restoring' state and cannot be used yet.

    RESTORE DATABASE [MiBase]
FROM DISK = N'D:\Backups\MiBase_full.bak'
WITH NORECOVERY, REPLACE,
     MOVE 'MiBase_Data' TO 'D:\Data\MiBase.mdf',
     MOVE 'MiBase_Log'  TO 'L:\Logs\MiBase.ldf';
  2. 2

    Apply the differential (if any)

    Also use NORECOVERY if there are still logs to apply. The differential rolls the database forward to that point.

    RESTORE DATABASE [MiBase]
FROM DISK = N'D:\Backups\MiBase_diff.bak'
WITH NORECOVERY;
  3. 3

    Apply the logs in order

    Restore each .trn in sequence. The last one uses RECOVERY. For point-in-time recovery, use STOPAT with the exact date and time.

    RESTORE LOG [MiBase]
FROM DISK = N'D:\Backups\MiBase_log.trn'
WITH RECOVERY, STOPAT = '2026-07-04T13:45:00';
  4. 4

    Bring the database online with RECOVERY

    The last command in the chain must use WITH RECOVERY to bring the database online. If you already set it on the previous log, do not repeat it.

    RESTORE DATABASE [MiBase] WITH RECOVERY;

// common mistake

REPLACE overwrites an existing database: it is destructive, so double-check the name. If you use RECOVERY too early, you break the chain and have to start over from the full backup. Log order matters: one out of sequence fails.

// when it's worth an expert

A botched restore during an emergency costs hours or days of data. At dba.mx we test restores before the incident and document the runbook, so recovery is a fixed-price certainty and not a gamble.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.