To create a new database from a .bak file in SQL Server, restore the backup under a new database name, either with the Restore Database wizard in SSMS or with a RESTORE DATABASE statement that uses WITH MOVE to give the data and log files new paths.
The restore itself is quick. The mistakes are in the details: logical file names, paths that do not exist and a name that already belongs to another database.
Table of contents
- What a .bak file is
- Restoring with the SSMS wizard
- Restoring with T-SQL
- Errors you will probably meet
- After the restore: the checklist
- Moving the data somewhere else
- Inspecting a backup before you trust it
- How this fits the rest of the stack
- FAQ
What a .bak file is
A .bak file is a SQL Server backup. A full backup contains the data and the part of the transaction log needed to bring it to a consistent state, plus metadata describing the original database and its files. It is not a database you can attach, and it is not portable text. It is a backup set that the server must restore.
Because the file carries the original database name and file layout, restoring to a different name on the same server needs a little care. Two databases cannot share the same physical files, so when you restore a backup of Sales as SalesCopy next to the original Sales, the new database needs different file paths. That is the most common stumbling block, and it produces errors about files already in use.
Before restoring, find out where the backup came from. Check the SQL Server version that created it. A backup can be restored to the same or a newer version, never to an older one, so a backup from a recent version cannot be restored on a server several releases behind. Also check the size of the files it will create, because a restore needs free disk space for the full database, not for the compressed backup.
If you only need to understand what is inside, you can inspect it without restoring, which the next sections show.
Restoring with the SSMS wizard
For a one-off task the graphical route is fine and shows you what will happen.
- Connect to the instance in SQL Server Management Studio.
- Right-click Databases and choose Restore Database.
- Under Source, select Device and browse to your .bak file.
- In the Destination box, type the new database name.
- Open the Files page and confirm the restore-as paths are unique.
- Review Options. Choose overwrite only if you mean to replace an existing database.
- Click OK.
Two details save time. When you pick the backup, the wizard fills in the original name as the destination. Change it, or you will try to overwrite the original. And on the Files page, make sure the physical file names do not collide with the original database’s files, especially when restoring a copy to the same server.
A tip from experience: leave the recovery state as the default restore with recovery unless you plan to apply further log backups. With recovery, the database is online and usable when the restore finishes. The existing guide on adding a .bak file to SQL Server walks the same screens with screenshots described.
Restoring with T-SQL
Scripts are repeatable and let you handle the file mapping exactly. First, read the logical file names inside the backup:
RESTORE FILELISTONLY
FROM DISK = N'C:\Backups\Sales.bak';The result lists LogicalName and PhysicalName for each file, typically one data file and one log file. Use the logical names in the restore:
RESTORE DATABASE SalesCopy
FROM DISK = N'C:\Backups\Sales.bak'
WITH MOVE N'Sales' TO N'D:\SQLData\SalesCopy.mdf',
MOVE N'Sales_log' TO N'D:\SQLLogs\SalesCopy_log.ldf',
RECOVERY,
STATS = 10;The MOVE clauses assign new physical paths to each logical file. The folders must exist and the SQL Server service account needs permission to write there. STATS prints progress in ten percent steps, which is reassuring on large restores.
If the backup file contains several backup sets, specify which with FILE = n. You can list them with RESTORE HEADERONLY. And before an important restore, run RESTORE VERIFYONLY against the file to check it is readable, which catches a truncated copy before it wastes an hour.
Errors you will probably meet
Most failures have a short list of causes.
The database already exists. Restoring over an existing database requires WITH REPLACE, which destroys it. Be certain before you use it, and prefer a new name.
Cannot overwrite the file. The physical file names collide with another database’s files. Use WITH MOVE with new names.
Operating system error 5, access denied. The SQL Server service account cannot read the backup folder or write to the target folder. Grant permissions, and avoid restoring from a user profile folder.
The backup was created by a newer version. You cannot restore it to an older server. Upgrade the server or ask for a script-based export.
Exclusive access could not be obtained. Something is connected to the database you are replacing. Set it to single user mode or disconnect sessions first.
Not enough disk space. The restored size exceeds the free space on the drive.
Read the message, because SQL Server’s restore errors are unusually descriptive. The error text usually tells you which file or which permission caused the failure.
After the restore: the checklist
A restored database is not finished until you check a few things.
- Orphaned users. Database users exist in the backup, but logins belong to the server. If you restore to a different server, users may be orphaned and unable to log in. Remap them with ALTER USER and the matching login.
- Compatibility level. A database restored to a newer server keeps its old compatibility level. Raise it when you have tested the application.
- Statistics and integrity. Run DBCC CHECKDB on important restores and update statistics.
- Collation. Differences between the database and server collation can break cross-database queries.
- Jobs, linked servers and credentials. These live outside the database and are not in the backup.
Then connect the application using a test connection string and run its main flows. A restore proves the bytes are intact. It does not prove the application works.
If the plan is to leave SQL Server for an open source engine, a backup file is the start of a migration, not the end. The post on SQL Server to PostgreSQL migration covers what changes.
Moving the data somewhere else
Sometimes the reason for restoring a .bak is to inspect the data and move it. A .bak cannot be read by other database engines, so you restore it into a SQL Server instance first, then export tables as CSV or generate scripts, or use a migration tool to copy data into the target engine.
Expect differences. Data types, identity columns, date handling, stored procedures and T-SQL specifics do not map one to one. Plan the schema conversion deliberately, load the data, then compare row counts and sample records between source and target.
RunxBuild offers managed MySQL and Postgres. It does not run SQL Server, so a .bak restore is something you do on a SQL Server instance you control, and the conversion is the path if you want to land on Postgres or MySQL. Once the data is moved, a managed instance handles backups, connection limits, user management and private networking, which the database docs describe. Treat the migration as a rehearsal first and a cutover second, and keep the original backup untouched until you are sure.
Inspecting a backup before you trust it
Never restore a backup you have not examined, particularly one received from someone else. Three read-only commands tell you most of what you need without changing anything.
RESTORE HEADERONLY FROM DISK = N'C:\Backups\Sales.bak';
RESTORE FILELISTONLY FROM DISK = N'C:\Backups\Sales.bak';
RESTORE VERIFYONLY FROM DISK = N'C:\Backups\Sales.bak';The header shows the database name, the backup type, when it was taken, the server version and whether it is compressed or encrypted. The file list shows sizes, so you can check free disk space before you start. The verify command checks that the backup set is complete and readable.
Confirm the backup type too. A differential or log backup cannot be restored on its own. It needs the full backup it depends on, restored first with NORECOVERY. If you receive only a differential, you do not have a restorable database.
Finally, restore into a test environment first when the data is sensitive or the source is unknown. A backup can contain triggers or procedures that run code, and you should know what is inside before it touches a production server.
How this fits the rest of the stack
A restore is the cheap half of a database move, and where the data lives next is the recurring cost. If you are considering a managed Postgres or MySQL instance as the destination, size it in the RunxBuild hosting calculator before you plan the cutover. When you are ready to try one, the RunxBuild dashboard is where a database is created.
Useful related references:
- Restoring a .bak File to SQL Server: SSMS and T-SQL, Step by Step
- Create a Git Branch From the Current One: switch, checkout, and Getting the Base Right
- How to Get a New Temporary IPv6 Address (and Why Privacy Extensions Exist)
- Databases on RunxBuild
FAQ
How do I create a new database from a .bak file?
Restore the backup under a new name, using the Restore Database wizard in SSMS or a RESTORE DATABASE statement with WITH MOVE to assign new file paths for the data and log files.
How do I find the logical file names in a .bak file?
Run RESTORE FILELISTONLY FROM DISK with the backup path. It returns the LogicalName and PhysicalName of each file, and you use the logical names in your MOVE clauses.
Why do I get an error that a file is already in use?
The backup’s original file paths belong to an existing database. Use WITH MOVE to restore the files to new paths, or restore onto a different server.
Can I restore a backup to an older SQL Server version?
No. A backup can be restored to the same or a newer version only. For an older server you need a script-based export or a data export instead.
Can other database engines read a .bak file?
No. Restore it into SQL Server first, then export tables or generate scripts to load the data into another engine.