This guide lists common restore errors you may see when importing a .bak in SQL Server Management Studio and how to resolve them. Focus is on GUI-based fixes, with small T-SQL snippets where helpful.

Quick checklist

• Confirm the .bak is on the SQL Server machine and readable by the SQL Server service account.

• If overwriting, check Options -> Overwrite the existing database (WITH REPLACE) and Close existing connections.

• Use the Files page to adjust Restore As file paths or Relocate all files to folder when paths conflict.

• Restore order matters: full -> differential -> logs, with NORECOVERY until the last step.

• Make sure your SQL Server version is the same or newer than the backup source.

• On SQL Express, check that the database is not larger than the edition size limit (10 GB).

1) Cannot open backup device / Access is denied (OS error 5 or 32)

**Cause:** The SQL Server service account cannot read the .bak or path is not local to the server instance.

**Fix:**

• Copy the .bak to a local folder on the server, for example `C:\Backups`.

• Grant Read to the SQL Server service account on that folder.

• In the Restore wizard, use Device -> Add and select that local file.

• Avoid restoring directly from your workstation path when the server is remote.

2) The database is in use / cannot obtain exclusive access

**Cause:** Active connections prevent the restore.

**Fix in GUI:** On the Options page, check **Close existing connections**.

**T-SQL (alternative):**

ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- run the restore
ALTER DATABASE [MyDB] SET MULTI_USER;

3) Directory lookup failed for file ... / file path conflicts

**Cause:** The backup references file paths that do not exist on this server or collide with existing files.

**Fix:** On the **Files** page:

• Edit **Restore As** paths to valid locations, or

• Check **Relocate all files to folder** and specify Data and Log folders that exist.

4) Logical file name mismatch / needing WITH MOVE

**Cause:** Different logical names or multiple databases sharing similar file names.

**Fix:** Same as #3. In GUI, set the **Restore As** paths so each file has a unique file name and directory.

5) Version mismatch: "The database was backed up on a server running version X. The database is not supported on this server."

**Cause:** Trying to restore a backup from a newer SQL Server version onto an older instance.

**Fix:**

• Restore on a **same-or-newer** SQL Server version, or

• Ask for a compatible export (bacpac) or data-level migration if upgrade is not possible.

6) Differential or log backup sequence errors

**Symptoms:** Errors about NORECOVERY or LSN sequence.

**Fix:**

• Restore the **full backup** first with **NORECOVERY**.

• Then restore the **differential** (and logs) also with **NORECOVERY** until the last step.

• Finally, run the last restore **WITH RECOVERY**.

**T-SQL example if needed:**

-- Full
RESTORE DATABASE [MyDB] FROM DISK='D:\Backups\MyDB_full.bak' WITH NORECOVERY;
-- Diff
RESTORE DATABASE [MyDB] FROM DISK='D:\Backups\MyDB_diff.bak' WITH NORECOVERY;
-- Last step
RESTORE DATABASE [MyDB] WITH RECOVERY;

7) Tail-of-log backup message when overwriting

**Cause:** SQL Server suggests taking a tail-log backup to preserve last changes.

**Fix in GUI:** If you intend to **replace** the database, on Options check **Overwrite the existing database (WITH REPLACE)** and **Close existing connections**. If the database is damaged and tail-log cannot be taken, WITH REPLACE allows restore without it.

8) Checksum or media errors during restore

**Cause:** Corrupted or incomplete backup file.

**Fix:**

• Re-copy the .bak from the source.

• If possible, verify on source with `RESTORE VERIFYONLY FROM DISK = ...`.

• Try restoring from an earlier good backup set or new backup.

9) Encrypted backup: "Cannot find server certificate with thumbprint..."

**Cause:** The backup was created using backup encryption on the source server.

**Fix:** You must have the corresponding **certificate/private key** (or asymmetric key) from the source instance to restore. Without it, the backup cannot be restored.

10) SQL Express database size limit

**Symptom:** Restore fails or database immediately goes SUSPECT due to size.

**Fix:** SQL Server Express has a **10 GB per-database limit**. Use a smaller backup, delete large data before backup, or restore on a higher edition.

11) Orphaned users after restore

**Symptom:** Users exist in the database but their logins are missing or mismatched; permissions errors occur.

**Fix:** Map the database user to an existing login or create the login and map it.

**T-SQL examples:**

-- Create a login if needed
CREATE LOGIN [app_login] WITH PASSWORD = 'StrongPass!123';

-- Map existing database user to the login
USE [MyDB];
ALTER USER [app_user] WITH LOGIN = [app_login];

-- See which users are mapped
SELECT dp.name AS db_user, sp.name AS server_login
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S','U','G') AND dp.name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys');

12) Database stuck in "Restoring" state

**Cause:** Last restore was done WITH NORECOVERY or chain not finalized.

**Fix:** Bring it online:

RESTORE DATABASE [MyDB] WITH RECOVERY;

13) Insufficient disk space or huge initial file sizes

**Cause:** The .mdf/.ldf sizes in the backup exceed free disk space.

**Fix:** Free space, restore to a larger volume, or ask for a smaller backup (e.g., purge old data before backup). You cannot shrink during restore; it must have space for original sizes.

14) Collation differences

**Symptom:** Sorting/comparison behavior changes or errors after restore.

**Fix:** Collation is stored with the database. If a different collation is required, plan a collation change post-restore or rebuild with desired collation.

15) Common SSMS wizard options to double-check

• **Source -> Backup sets to restore**: choose the correct date/time.

• **Destination -> Database**: verify the target name is correct.

• **Files**: adjust **Restore As** to valid paths and unique file names, or use **Relocate all files to folder**.

• **Options**: choose **WITH RECOVERY** for final step, **WITH REPLACE** only when overwriting on purpose, and **Close existing connections** when needed.