Restore Database with Microsoft Management Studio

Restoring a database looks trivial until it refuses to go through. These are the steps in SQL Server Management Studio, plus the two gotchas that trip everyone up at least once: exclusive access and file paths.

Choose Device as the source, then Add File and locate the .bak file. (The original post used the older “From device” radio-button UI from SQL Server 2005/2008 — current SSMS uses the Source → Device selector, same idea.)

Tick the backup set you want, then go to Options and check Overwrite the existing database (WITH REPLACE).

If the backup comes from another machine, check the Restore As file paths on the Files page. SQL Server will try to recreate the .mdf/.ldf files at the exact paths recorded in the backup — if those folders don’t exist on your machine (or the drive letters don’t exist at all), the restore fails. Change the paths to locations that exist on your server.

Click OK and you’re done.

“Exclusive access could not be obtained because the database is in use”

The classic failure. The restore needs exclusive access, but other connections are holding the database open — an app pool, SSMS query windows, even Object Explorer’s own details view.

The clean fix is to kick everyone off and restore, in one statement:

USE master;
ALTER DATABASE yourdatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE yourdatabasename FROM DISK = 'C:\backups\yourdb.bak' WITH REPLACE;
ALTER DATABASE yourdatabasename SET MULTI_USER;

Note on the original script: it did the opposite of what you want — SET MULTI_USER allows connections rather than blocking them, and it followed with a DROP DATABASE, which destroys the database instead of restoring it. If you truly want the database gone first, drop it after setting single-user, but for a restore you never need to: RESTORE with ROLLBACK IMMEDIATE handles everything.

If connections keep coming back, verify what’s holding the database via:

SELECT spid, loginame, hostname, program_name
FROM sys.sysprocesses
WHERE dbid = DB_ID('yourdatabasename');

Other common restore failures worth knowing:

• “The media family on device is incorrectly formed” — the .bak file is corrupt or (very common in 2008-era and still today) a RAR/ZIP that was never actually extracted.
• “The backup set holds a backup of a database other than the existing” — you’re restoring over a different database; WITH REPLACE fixes this too.
• Tail of the log — on a production server with FULL recovery, consider backing up the tail of the log first so you don’t lose recent transactions; the restore dialog offers this under Options.
• Version mismatch — you cannot restore a backup from a newer SQL Server version onto an older instance. Ever. Restore on a matching-or-newer version, or use .bak-level compatibility tools.