Overview
This guide covers common errors you may encounter when restoring a .bak file in SQL Server Management Studio (SSMS) and how to resolve them. The focus is on GUI-based fixes, with T-SQL snippets included 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 paths or use Relocate all files to folder when paths conflict
- Restore order matters: full, then differential, then logs - use NORECOVERY until the final step
- Ensure your SQL Server version is the same or newer than the backup source
- On SQL Express, confirm the database does not exceed the 10 GB edition size limit
1. Cannot Open Backup Device / Access is Denied (OS Error 5 or 32)
Cause: The SQL Server service account cannot read the .bak file, or the path is not local to the server instance.
Fix:
- Copy the .bak to a local folder on the server (example:
C:\Backups) - Grant Read permission to the SQL Server service account on that folder
- In the Restore wizard, use Device > Add and select the local file
- Avoid restoring directly from a workstation path when the SQL Server is remote
2. The Database is in Use / Cannot Obtain Exclusive Access
Cause: Active connections are preventing the restore.
Fix (GUI): On the Options page, check Close existing connections.
Fix (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 / File Path Conflicts
Cause: The backup references file paths that do not exist on this server or conflict with existing files.
Fix: On the Files page, either edit the 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
Cause: Different logical names or multiple databases sharing similar file names.
Fix: Same as issue 3. Use the Files page to set unique Restore As file names and directories for each file.
5. Version Mismatch
Cause: Attempting to restore a backup from a newer SQL Server version onto an older instance.
Fix:
- Restore on a same-or-newer SQL Server version
- If an upgrade is not possible, request a compatible export (.bacpac) or perform a data-level migration
6. Differential or Log Backup Sequence Errors
Cause: NORECOVERY or LSN sequence issues when restoring a backup chain.
Fix: Restore in the correct order using NORECOVERY until the final step:
-- Full backup
RESTORE DATABASE [MyDB] FROM DISK='D:\Backups\MyDB_full.bak' WITH NORECOVERY;
-- Differential backup
RESTORE DATABASE [MyDB] FROM DISK='D:\Backups\MyDB_diff.bak' WITH NORECOVERY;
-- Final step - bring database online
RESTORE DATABASE [MyDB] WITH RECOVERY;7. Tail-of-Log Backup Message When Overwriting
Cause: SQL Server is prompting to take a tail-log backup to preserve recent changes before overwriting.
Fix: If you intend to replace the database, on the Options page check Overwrite the existing database (WITH REPLACE) and Close existing connections. If the database is damaged and a tail-log backup cannot be taken, WITH REPLACE allows the restore to proceed without it.
8. Checksum or Media Errors During Restore
Cause: The backup file is corrupted or incomplete.
Fix:
- Re-copy the .bak from the source
- Verify the backup on the source server using
RESTORE VERIFYONLY FROM DISK = ... - Restore from an earlier known-good backup set if available
9. Encrypted Backup - Cannot Find Server Certificate
Cause: The backup was created using backup encryption on the source server.
Fix: You must have the corresponding certificate and private key (or asymmetric key) from the source instance. Without it, the backup cannot be restored.
10. SQL Express Database Size Limit
Cause: SQL Server Express has a 10 GB per-database limit.
Fix: Use a smaller backup, purge large data before backing up, or restore on a higher SQL Server edition.
11. Orphaned Users After Restore
Cause: Database users exist but their server logins are missing or mismatched, causing permission errors.
Fix:
-- 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];
-- View current user-to-login mappings
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: The last restore was performed WITH NORECOVERY and the chain was not finalized.
Fix:
RESTORE DATABASE [MyDB] WITH RECOVERY;13. Insufficient Disk Space
Cause: The .mdf and .ldf sizes defined in the backup exceed available disk space on the target server.
Fix: Free up disk space, restore to a larger volume, or request a smaller backup with old data purged beforehand. File sizes cannot be reduced during the restore process.
14. Collation Differences
Cause: The backup was taken from a database with a different collation, causing sorting or comparison behavior changes after restore.
Fix: Collation is stored with the database. If a different collation is required, plan a collation change post-restore or rebuild the database with the desired collation.
SSMS Wizard Options to Always Double-Check
- Source > Backup sets to restore: confirm the correct date and time is selected
- Destination > Database: verify the target database name is correct
- Files: adjust Restore As paths to valid locations with unique file names, or use Relocate all files to folder
- Options: set WITH RECOVERY for the final restore step, WITH REPLACE only when intentionally overwriting, and Close existing connections when needed
