How to restore a SQL Server database from a .bak file

Restoring a .bak file is simple when you restore to a new database name, and risky when you restore over a database people are using. This guide shows both, plus how to apply differential and log backups in the right order.

First, look inside the backup file

Before restoring, check what the file contains. These commands read the file without changing anything.

-- What backups are in the file, from which server version and when
RESTORE HEADERONLY FROM DISK = N'D:\SQLBackups\Sales_FULL_20260927.bak';

-- The logical file names you need for MOVE
RESTORE FILELISTONLY FROM DISK = N'D:\SQLBackups\Sales_FULL_20260927.bak';

If either command fails with error 3241, the file is damaged or from a newer SQL Server version. See fix error 3241.

Restore in SSMS

1. Right-click Databases and choose Restore Database.
2. Select Device, click the ... button, then Add, and pick the .bak file. The file must be readable by the SQL Server service account.
3. In Destination > Database, type a new name, for example Sales_restored, unless you really intend to replace the existing database.
4. On the Files page, tick Relocate all files to folder so the new database does not try to use the original database's file names.
5. Click OK.

Restore with T-SQL under a new name

Use the logical names from RESTORE FILELISTONLY (the LogicalName column):

RESTORE DATABASE [Sales_restored]
FROM DISK = N'D:\SQLBackups\Sales_FULL_20260927.bak'
WITH MOVE N'Sales'     TO N'E:\SQLData\Sales_restored.mdf',
     MOVE N'Sales_log' TO N'E:\SQLLogs\Sales_restored_log.ldf',
     CHECKSUM, STATS = 10, RECOVERY;

Restoring under a new name is the safe default. You can compare data, copy back what you need, and only then decide whether to swap databases.

Restore full + differential + log backups

Every restore in a sequence except the last uses NORECOVERY, which keeps the database waiting for more backups. The last one uses RECOVERY to bring it online.

RESTORE DATABASE [Sales_restored] FROM DISK = N'D:\SQLBackups\Sales_FULL_20260921.bak'
  WITH MOVE N'Sales' TO N'E:\SQLData\Sales_restored.mdf',
       MOVE N'Sales_log' TO N'E:\SQLLogs\Sales_restored_log.ldf',
       NORECOVERY;

-- Only the most recent differential is needed
RESTORE DATABASE [Sales_restored] FROM DISK = N'D:\SQLBackups\Sales_DIFF_20260926.bak'
  WITH NORECOVERY;

-- Every log backup after that differential, oldest first
RESTORE LOG [Sales_restored] FROM DISK = N'D:\SQLBackups\Sales_LOG_20260926_1000.trn' WITH NORECOVERY;
RESTORE LOG [Sales_restored] FROM DISK = N'D:\SQLBackups\Sales_LOG_20260926_1100.trn' WITH NORECOVERY;

RESTORE DATABASE [Sales_restored] WITH RECOVERY;

Restore to a point in time

With FULL recovery and log backups you can stop just before a mistake, such as a bad DELETE. Add STOPAT to the log restores:

RESTORE LOG [Sales_restored]
FROM DISK = N'D:\SQLBackups\Sales_LOG_20260926_1100.trn'
WITH STOPAT = N'2026-09-26T10:42:00', RECOVERY;

Replacing an existing database

SQL Server protects you from overwriting a live database. You will see errors 3154 or 3159. If you are certain, take a tail-log backup first (FULL recovery), then restore WITH REPLACE. Everyone must be disconnected:

ALTER DATABASE [Sales] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [Sales] FROM DISK = N'D:\SQLBackups\Sales_FULL_20260927.bak'
  WITH REPLACE, STATS = 10;
ALTER DATABASE [Sales] SET MULTI_USER;

After the restore

• Fix orphaned users when restoring to a different server. Database users are linked to logins by SID, which differs between servers: ALTER USER [appuser] WITH LOGIN = [appuser];
• Check integrity: DBCC CHECKDB ([Sales_restored]) WITH NO_INFOMSGS;
• Remember the version rule. You can restore to the same or a newer SQL Server version, never to an older one.