Restore a SQL Server database with T-SQL or Management Studio: WITH MOVE, NORECOVERY chains, point-in-time recovery, and fixes for errors such as 3154 and 3201.
To restore a full backup over the database it came from, on the same server:
USE master;
GO
RESTORE DATABASE SalesDB
FROM DISK = N'D:\Backup\SalesDB_full.bak'WITH REPLACE, RECOVERY, STATS =10;
REPLACE overwrites the existing database, RECOVERY brings it online when the restore finishes, and STATS = 10 prints progress every 10 percent. That is the simplest case. Restoring to another server, to a new name, or to a point in time needs a few more options, covered below along with the errors you are most likely to hit.
The path in FROM DISK is read by the SQL Server service on the server, not by your workstation. D:\Backup means the server's D: drive.
Step 1: look inside the backup file
Before restoring an unfamiliar file, ask it what it contains. Neither command changes anything:
RESTORE HEADERONLY FROM DISK = N'D:\Backup\SalesDB_full.bak';
RESTORE FILELISTONLY FROM DISK = N'D:\Backup\SalesDB_full.bak';
HEADERONLY returns one row per backup set in the file. The useful columns are Position, BackupType (1 = full, 2 = log, 5 = differential), DatabaseName, BackupFinishDate, FirstLSN, LastLSN and DatabaseVersion.
FILELISTONLY lists the database files inside the backup. You need the LogicalName values for the next step:
LogicalName PhysicalName Type
SalesDB E:\SQLData\SalesDB.mdf D
SalesDB_log F:\SQLLogs\SalesDB_log.ldf L
If HEADERONLY shows more than one row, the file holds several backups, and a restore without further instruction uses the first, which is the . Pick the one you want with , where is the .
It's caching by design, not a leak, but the default limit is 'everything'. How to set max server memory and tell real memory pressure from a full buffer pool.
Step 2: restore to a new name or a new server with MOVE
By default SQL Server recreates each file at the path recorded in the backup. That fails when the drive or folder does not exist on this server, and it is wrong when you want a copy next to the original. MOVE maps each logical file to a new physical path:
RESTORE DATABASE SalesDB_Copy
FROM DISK = N'D:\Backup\SalesDB_full.bak'WITH MOVE N'SalesDB'TO N'E:\SQLData\SalesDB_Copy.mdf',
MOVE N'SalesDB_log'TO N'F:\SQLLogs\SalesDB_Copy_log.ldf',
RECOVERY, STATS =10;
Include one MOVE for every row that FILELISTONLY returned. To find the instance's default folders:
SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS data_path,
SERVERPROPERTY('InstanceDefaultLogPath') AS log_path;
On Linux and in containers the paths look like /var/opt/mssql/data/SalesDB_Copy.mdf.
Step 3: apply differential and log backups with NORECOVERY
To restore more than one backup, every step except the last must leave the database in the restoring state so it can accept the next file. That is what NORECOVERY does:
RESTORE DATABASE SalesDB
FROM DISK = N'D:\Backup\SalesDB_full.bak'WITH REPLACE, NORECOVERY, STATS =10;
RESTORE DATABASE SalesDB
FROM DISK = N'D:\Backup\SalesDB_diff.bak'WITH NORECOVERY;
RESTORE LOG SalesDB
FROM DISK = N'D:\Backup\SalesDB_log_1415.trn'WITH NORECOVERY;
RESTORE LOG SalesDB
FROM DISK = N'D:\Backup\SalesDB_log_1430.trn'WITH NORECOVERY;
RESTORE DATABASE SalesDB WITH RECOVERY; -- bring it online
The order is: the full backup, the latest differential taken after that full, then every log backup taken after that differential, in sequence. Finishing with a separate WITH RECOVERY statement is a good habit, because it lets you add one more log file if you find you need it.
RECOVERY is the default. If you omit the option on the first restore, the database comes online immediately and refuses the differential and log backups; you have to start again from the full backup.
A database shown as (Restoring...) in Management Studio is simply waiting for the next file or for that final RESTORE DATABASE ... WITH RECOVERY.
Step 4: restore to a point in time
If someone deleted data at 14:32, you can stop the restore just before it. This needs the FULL (or BULK_LOGGED) recovery model and an unbroken run of log backups covering that moment.
If the damaged database is still online, first back up the log records that have not been backed up yet. NORECOVERY here puts the database into the restoring state so nothing else changes:
BACKUP LOG SalesDB
TO DISK = N'D:\Backup\SalesDB_tail.trn'WITH NORECOVERY;
Then restore the chain, with STOPAT on the log restores:
RESTORE DATABASE SalesDB
FROM DISK = N'D:\Backup\SalesDB_full.bak'WITH NORECOVERY;
-- then the latest differential and each log backup in order, for example:
RESTORE LOG SalesDB
FROM DISK = N'D:\Backup\SalesDB_log_1430.trn'WITH NORECOVERY, STOPAT ='2026-10-08T14:31:00';
RESTORE LOG SalesDB
FROM DISK = N'D:\Backup\SalesDB_tail.trn'WITH RECOVERY, STOPAT ='2026-10-08T14:31:00';
STOPAT is in the server's local time.
Putting the same STOPAT on every log restore is safe. If a log backup ends before that time, it is applied in full and the database stays in the restoring state, ready for the next one.
You cannot stop at a point inside a full or differential backup, only inside a log backup.
When the goal is to recover a few rows, do not overwrite production. Restore to a new name with MOVE and STOPAT, then copy the rows across. It is safer, and the live database stays online meanwhile.
Common errors and what they mean
Error
Message (abridged)
Cause and fix
3154
The backup set holds a backup of a database other than the existing database
The target name already exists and is a different database. Restore under a new name with MOVE, or add WITH REPLACE if you really mean to overwrite it.
3159
The tail of the log for the database has not been backed up
The target is in FULL recovery with log records not yet backed up. Take a tail-log backup (BACKUP LOG ... WITH NORECOVERY), or add WITH REPLACE to discard them.
3101
Exclusive access could not be obtained because the database is in use
Other sessions are connected, possibly your own query window. See below.
3201
Cannot open backup device. Operating system error 5 (Access is denied), 3 (path not found) or 2 (file not found)
The SQL Server service account cannot see or read the file. Check that the path exists on the server and grant that account read permission. Mapped drive letters do not exist for the service; use a UNC path.
3156, 5133
File cannot be restored to ... Use WITH MOVE; Directory lookup for the file failed
The original folder does not exist here. Add MOVE clauses.
1834
The file cannot be overwritten. It is being used by database ...
Your target path belongs to another database, typically when restoring a copy without changing the file names. Use different names in MOVE.
3169
The database was backed up on a server running version ... That version is incompatible with this server
The backup comes from a newer SQL Server version. A backup can never be restored to an older version.
3241
The media family on device is incorrectly formed
The file is not a backup this instance can read: often one made on a newer version, or a file damaged in transfer.
4305
The log in this backup set begins at LSN ..., which is too recent to apply to the database
A log backup is missing or out of order. Restore the earlier one first; compare FirstLSN and LastLSN from RESTORE HEADERONLY.
33111
Cannot find server certificate with thumbprint ...
The database uses TDE or the backup is encrypted. Restore the certificate and private key to this server first.
For error 3101, switch your own session to master, then disconnect everyone else and restore:
USE master;
GO
ALTER DATABASE SalesDB SET SINGLE_USER WITHROLLBACK IMMEDIATE;
RESTORE DATABASE SalesDB
FROM DISK = N'D:\Backup\SalesDB_full.bak'WITH REPLACE, RECOVERY;
ALTER DATABASE SalesDB SET MULTI_USER;
ROLLBACK IMMEDIATE rolls back open transactions and drops the connections, so warn users first.
Be deliberate with REPLACE. It switches off the safety checks behind errors 3154 and 3159, which exist to stop you overwriting the wrong database or throwing away unbacked-up log.
After the restore
Confirm the database is online and healthy:
SELECT name, state_desc, recovery_model_desc, compatibility_level
FROM sys.databases
WHERE name = N'SalesDB';
DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS;
Then deal with what a restore onto a different server leaves behind:
Orphaned users. Database users are linked to server logins by SID. On another server those logins may not exist, or have different SIDs, and the application gets login failures. Find them and re-link each one:
USE SalesDB;
GO
SELECT dp.name
FROM sys.database_principals AS dp
LEFTJOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE dp.type ='S'AND dp.authentication_type =1AND sp.sid ISNULL;
ALTERUSER app_user WITH LOGIN = app_user;
Owner. The database is owned by whoever ran the restore. Change it with ALTER AUTHORIZATION ON DATABASE::SalesDB TO sa; or your standard owner.
Compatibility level. A database restored onto a newer version is upgraded internally but keeps its old compatibility level. Raise it with ALTER DATABASE SalesDB SET COMPATIBILITY_LEVEL = ... once you have tested.
Backups. Take a fresh full backup to start a new backup chain on this server.
Using Management Studio
Right-click Databases and choose Restore Database. Select Device, click ... and add the backup file. Management Studio reads the header and lists the backup sets. Then:
Files page: tick Relocate all files to folder, or edit each Restore As path. This is MOVE.
Options page: Overwrite the existing database (WITH REPLACE), the Recovery state list (RECOVERY, NORECOVERY, STANDBY), and Close existing connections to destination database.
Timeline button on the General page: pick a point in time.
Click Script before OK to see the T-SQL it will run. That is the quickest way to learn the syntax, and to get a script you can keep.
Permissions and limits
If the database does not exist yet, you need CREATE DATABASE permission. If it does, you need to be in sysadmin or dbcreator, or be the database owner.
You can restore to the same or a newer version of SQL Server, never to an older one. Backups of master, model and msdb can only be restored to the version they came from.
Azure SQL Database does not support RESTORE from a backup file; use its built-in point-in-time restore, or import a BACPAC. Azure SQL Managed Instance restores native backups from URL only.
NOLOCK doesn't just risk dirty reads. It can skip rows, count rows twice and fail with error 601. Read Committed Snapshot Isolation removes the blocking without the wrong answers.
Execution plans tell you exactly how SQL Server ran your query. Learn which operators matter, how to spot bad estimates and what the warnings really mean.
