Restoring a .bak File to SQL Server: SSMS and T-SQL, Step by Step

Restoring a .bak file into SQL Server means telling the engine to rebuild a database from a backup file, and there are exactly two ways to do it: the Restore Database dialog in SQL Server Management Studio, or the RESTORE DATABASE statement in plain T-SQL. Both do the same thing underneath, and both fail for the same handful of reasons: the SQL Server service account cannot read the folder the .bak file sits in, the backup was taken on a newer version of SQL Server than the one restoring it, or the backup’s internal file names do not match any path that exists on the target machine, which is what WITH MOVE exists to fix.

This covers both paths, SSMS and T-SQL, side by side, plus RESTORE FILELISTONLY for inspecting a backup before you restore it and the specific errors that come up most often, with what each one actually means rather than just the fix. It assumes a .bak file you already have and a SQL Server instance, local or remote, that you can connect to with sysadmin or db_creator rights.

Table of contents

• What’s actually inside a .bak file
• Restoring through SSMS, step by step
• The same restore in plain T-SQL
• RESTORE FILELISTONLY: checking a backup before you commit to it
• WITH MOVE: when the backup’s file paths don’t exist on this machine
• The errors that show up most, and what each one actually means
• How this fits the rest of the stack
• FAQ

What’s actually inside a .bak file

A SQL Server backup file, the .bak extension, is not a snapshot of a folder the way a filesystem backup is; it is SQL Server’s own binary format for the pages that make up a database at the moment BACKUP DATABASE ran, plus enough metadata to rebuild the database’s file structure, permissions, and log state from nothing. A single .bak file can even hold more than one backup, if full and differential backups were appended to the same file over time, which matters when a restore dialog shows more than one backup set to choose from instead of just one.

Two commands are worth knowing before you restore anything from an unfamiliar .bak file: RESTORE HEADERONLY reads the backup’s metadata, including when it was taken and which SQL Server version created it, without touching any database, and RESTORE FILELISTONLY, covered in detail further down, lists the logical and physical file names the backup expects to write. Running both first, on a .bak file from an unfamiliar source, answers most of the questions a restore attempt would otherwise fail on halfway through.

Restoring through SSMS, step by step

Connect to the target instance in SQL Server Management Studio, right-click Databases in Object Explorer, and choose Restore Database. Under Source, select Device, click the browse button next to it, click Add, and navigate to the .bak file. SSMS reads the file and lists the backup sets it contains under Backup sets to restore; for a file with a full backup plus later differentials, checking the most recent set restores through that point automatically, since SQL Server knows which earlier sets it depends on.

On the Files page, confirm the target database name and check whether the file paths SSMS proposes for the data and log files actually exist on this server; if they do not, that is exactly the WITH MOVE situation covered below, and SSMS lets you edit the paths directly in this dialog before restoring rather than requiring T-SQL. On the Options page, Overwrite the existing database matters if a database with that name already exists, and the Recovery state should stay on RESTORE WITH RECOVERY unless another differential or log backup is coming right after this one.

Click OK, and SSMS runs the equivalent RESTORE DATABASE statement for you. The progress bar at the bottom is the only feedback during the restore itself; for a large database this can run for minutes with no other visible activity, which is normal.

The same restore in plain T-SQL

The dialog above is a GUI around a single statement, and running it directly is faster once the file paths are known, and easier to script or run against multiple servers than clicking through the dialog each time.

RESTORE DATABASE MyDatabase
FROM DISK = N'D:\Backups\MyDatabase.bak'
WITH FILE = 1,
  MOVE N'MyDatabase' TO N'D:\SQLData\MyDatabase.mdf',
  MOVE N'MyDatabase_log' TO N'D:\SQLData\MyDatabase_log.ldf',
  REPLACE,
  STATS = 10;

FILE = 1 selects the first backup set in the file when there is more than one; STATS = 10 prints progress in ten percent increments instead of leaving the console silent; REPLACE allows overwriting an existing database of the same name and is required the first time you restore over something that already exists. The two MOVE clauses relocate the logical files to actual paths on this server, the T-SQL equivalent of editing the Files page in the SSMS dialog, and required whenever the backup’s original paths do not exist on the machine doing the restore.

RESTORE FILELISTONLY: checking a backup before you commit to it

Before restoring an unfamiliar .bak file, run RESTORE FILELISTONLY against it to see the logical file names it expects and the physical paths it was originally written from, without actually restoring anything.

RESTORE FILELISTONLY
FROM DISK = N'D:\Backups\MyDatabase.bak';

The result set’s LogicalName column is exactly what belongs on the left side of each MOVE clause, and PhysicalName shows the original server’s paths, which almost never match the target server and is the fastest way to confirm WITH MOVE is needed before the restore fails partway through with a file-not-found error instead.

WITH MOVE: when the backup’s file paths don’t exist on this machine

A .bak file records the exact file paths the database used on the server it was backed up from, and RESTORE DATABASE tries to recreate the database at those same paths by default. That works only when restoring to an identical server layout, rare outside of disaster recovery onto matching hardware. Restoring a backup from a server with a D:\SQLData folder onto one that only has C:\ fails immediately unless the paths are remapped, and WITH MOVE is that remapping, one clause per logical file, mdf and ldf both.

The values from RESTORE FILELISTONLY plug directly in: the LogicalName goes on the left of each MOVE, and a real, existing folder on the target server’s disk goes on the right. Get the logical name wrong and SQL Server reports it cannot find that logical file in the backup; get the destination folder wrong and the restore fails with a path-not-found error instead, both before any data actually moves.

The errors that show up most, and what each one actually means

A handful of errors account for nearly every failed restore, and each one points to a specific, fixable cause rather than a generic file problem:

• Access is denied. The SQL Server service account, not your own Windows login, needs read permission on the folder containing the .bak file. Granting your own user access does nothing if the service runs under a different account.
• Backed up on a server running a version incompatible with this server. SQL Server backups are forward-compatible only. A .bak file from a newer version cannot restore onto an older SQL Server; the fix is restoring to an equal or newer version, never older.
• Exclusive access could not be obtained because the database is in use. Another connection, often SSMS’s own Object Explorer, has the target database open. Closing other query windows against it, or switching the database to single-user mode first, clears this.
• The file cannot be overwritten; it is being used by another database. The physical path in a MOVE clause collides with a file another database already owns. Point the MOVE clause at a genuinely new path instead.

Reading the actual error text carefully, rather than retrying the same restore, resolves most of these on the first attempt, since SQL Server’s restore errors are unusually specific about which of the four situations above actually happened.

How this fits the rest of the stack

None of this is a workflow RunxBuild runs; we host Postgres and MySQL as managed databases, not SQL Server. For anyone weighing whether to keep restoring .bak files onto self-managed SQL Server instances at all, the SQL Server to PostgreSQL migration guide covers the type mapping and cutover plan, and the RunxBuild hosting calculator prices a managed Postgres instance with backups handled automatically, for whenever restoring backups by hand stops being worth the afternoon it costs.

Useful related references:

• SQL Server to PostgreSQL Migration
• Free SQL Server Database Online
• PostgreSQL Backup
• Backup and Recovery

FAQ

How do I restore a .bak file in SQL Server?

In SSMS, right-click Databases, choose Restore Database, select Device as the source, browse to the .bak file, and click OK, adjusting file paths on the Files page if needed. In T-SQL, RESTORE DATABASE with FROM DISK pointing at the file, plus MOVE clauses for the data and log files, does the same thing.

Why does my SQL Server restore fail with an access denied error?

The SQL Server service account, not your own Windows login, needs read permission on the folder holding the .bak file. Granting yourself access to the folder does nothing if the SQL Server service runs under a different Windows account, which it usually does.

What does WITH MOVE do in a SQL Server restore?

It remaps a backup’s logical files to actual file paths on the server doing the restore. A .bak file records the original server’s file paths, and RESTORE DATABASE tries to recreate the database there by default, which fails whenever the target server has a different folder layout, the normal case outside of disaster recovery onto identical hardware.

Can I restore a .bak file to an older version of SQL Server?

No. SQL Server backups are forward-compatible only, meaning a backup taken on a newer version cannot restore onto an older one. The fix is restoring to an equal or newer SQL Server version than the one that created the backup, or exporting the data through a different method if the version genuinely cannot move forward.

What is RESTORE FILELISTONLY used for?

It reads a .bak file’s list of logical and physical file names without restoring anything, the fastest way to check what a WITH MOVE clause needs before running the actual restore. Running it first on an unfamiliar backup avoids a failed restore partway through from a file path that does not exist on the target server.