Restore (Import) database from .bak file in SQL server (With & without scripts)
Usually, we restore the database either using scripts or using .bak file of the database, executing is script is quite easy,just open a ‘New Query’ window in your SQL server version and copy-paste your script which may have data or just tables schema(as selected by your while creating scripts), so in this post, I will explain you about restoring the database using .bak in SQL server, step by step with images.
Step 1: I suppose you already have the .bak file of the database which you want to restore. If you don’t have it, create .bak file using sql server or just download the demo database from this link https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
Step 2: Move your .bak in a drive/folder which can be accessed by anyone or you can say which doesn’t require any special rights.
Step 3: Open your SQL server(Express version or any other version I am using Express version), connect with your sql server for which you want to restore the database, right-click on the «Databases» and click «Restore Database»
Step 4: Now select the «Device» radio button and then click on browse(. ) button to locate the .bak file stored in your hard drive.
Step 5: As soon as you will click browser(. ) but, a dialog box like below will appear, click «Add» inside that dialog box
Step 6: Locate the .bak file, in this example it’s AdventureWorks2012.bak stored in D:\, after selecting the file, click «OK»
Step 7: You will see as image below, Click «Ok»
Step 8: We are all done now, database file (.bak) is located and now just need to Click «OK», rest SQL server will handle, so click on «OK» & wait for few seconds until SQL server restores database.
Once done, you will see a pop-up message which shows success message.
That’s it we are done, you can see the database is restored succesffuly.
Another Method in SQL Server to restore database from bak file using script
In the above method to restore database in sql server is to restore database from bak file using script, so suppose here we have .bak file in D:\, we can run script as below
Using T-SQL
Connect to the Database Engine.
From the Standard bar, click New Query.
In the RESTORE statement, specify a logical or physical backup device to use for the backup operation. This example restores from a disk file that has the physical name
the above script, will restore the database using the specified file. If the database already exists it will overwrite the files. If the database does not exist it will create the database and restore the files to same location specified in the backup.
Note: You might get error
In this case, it means you are missing .ldf/.mdf files of the above backup, to get it, you need to run the below command
Once you will execute the above command, you will get location of .mdf/.ldf like «C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf» with logical name «AdventureWorks2012_Data», use it in below query to restore database.
Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)
The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off.
Restoring a differential database backup
This example restores a database and differential database backup of the MyAdvWorks database.
How to Restore Database in SQL Server from .Bak File Step By Step
Written By Charanjeet Kaur 
Approved By Raja Jegan R
Updated on December 30, 2022
Min Reading 8 Min
Summary: This article will describe step-by-step process to restore database in SQL Server from .bak file. You can try restoring the backup file by using SQL Server Management Studio (SSMS), executing Transact-SQL (T-SQL) query, or with PowerShell command. If backup isn’t available or is obsolete, or the database is corrupt, Stellar repair for MSSQL — Technician software can come in handy. The technician comprises tools to resolve SQL Server database issues without prolonged downtime. The tools can be used to recover data from corrupted backup, repair a corrupted SQL database file (.mdf/.ndf), and reset password of a database file.
Many SQL Server Database Administrators (DBAs) struggle repeatedly with errors like abrupt termination, database connection or transient errors, problem with drivers, malicious software attack, etc. Such errors can render a database unusable. While rebooting the SQL server may resolve the problem in some cases, using a backup (.bak) file is the most feasible option to restore SQL database (db).
Restore Database in SQL Server from .BAK File
SQL Server database restoration is performed for Full, Differential, File or Filegroup database backup with the help of SQL Server Management Studio (SSMS), T-SQL, or by using PowerShell .
Things You Need to Know Before Backup Restoration
Before you proceed with the backup restoration process, you will need to:
- Close all the active connections before attempting to restore SQL Server database from .BAK file. Doing so will prevent the restore process from failing due to active database connections.
- Backups that are created on recent SQL Server versions cannot be restored to earlier versions of SQL Server.
- Backup the active transaction log, before performing the full database backup. This is because, if the active transaction log (also referred to as the tail of the log) becomes unavailable, all transactions in that log will be lost.
- When you want to restore a db from another server instance, you will need to manage metadata to make the db available on another server. Refer to this link for more information.
Methods to Restore SQL Server Database from .BAK File
Method 1 – Restore SQL Database using SSMS
If you have SSMS installed on your system, follow these steps to restore your SQL database from the backup (.bak) file:
Step 1: Open SSMS and connect to an instance of SQL Server.
Step 2: Next, go to Object Explorer and click the Server Name to expand the Server tree.
Step 3: Navigate to Databases and open the database you want to restore in SQL Server.
Step 4: Right-click the Database , and then click Restore Database .
.jpg)
Figure 1 — Restore Database Option
Step 5: The Restore Database window is displayed. From the General page, under Source section, select any of these options:
a. Choose the Database option, and then select the database you want to restore from the drop-down list.
| Note: When the backup is taken from another server, the destination server won’t contain the backup history information for the selected database. However, you can manually specify the database file or device by selecting the Device option. |
b. Choose the Device option, and then click the ellipses (. ) to find your backup file.
- From ‘Select backup devices’ window, choose File as backup media, and then click Add .
- Locate and select the .BAK file you want to restore, and then click OK .
- Again click OK to return to the Restore Database window.
Figure 2 — Locate SQL Database Backup File
- From ‘Select backup devices’ window, choose File as backup media, and then click Add .
Figure 3 – Select Add to Navigate to .BAK File
- Locate and select the .BAK file you want to restore, and then click OK .
Figure 4 – Select the Backup File
- Again click OK to return to the ‘Restore Database’ window.
Figure 5 – Selected Backup File in Specify Backup Window
Step 6: The name of the database to be restored is displayed in the Database box under Destination section. And, information of the selected backup file will be added in the Backup sets to restore section.
.jpg)
Figure 6 – Backup File in Restore Database Window
All the information needed to restore the database from backup file has been entered. You just need to click on the OK button to restore the database. However, SQL .bak files store information about the location of data files, and in case of any problems with those files, such as conflicting data file names” or “the destination directory does not exist” can result in an error.
Such problems usually occur when restoring SQL database from a .BAK file created on another SQL Server installation. Changing the file settings from the Options page can help overcome any such problem. To do so, follow these steps:
a. Select Options under Select a Page .
.jpg)
Figure 7 — Locate SQL Database Backup File
b. On the Options page, do the following:
- Under the Restore options section, select the ‘ Overwrite the existing database (WITH REPLACE) ’ option.
Figure 8 — Restore Options Section
- Under the Recovery State section, choose ‘Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)’. .
.jpg)
Figure 9 — Recovery state Options
Step 7: To proceed with the restore process, hit the OK button.
Step 8: The window with restore progress is displayed.
Figure 10 — Restore Process in Progress Window
Step 9: Click OK when the ‘restore of database completed successfully’ message pops up.
Figure 11 — Database Restoration Completed Message
Your SQL database will be successfully restored.
Method 2 – Restore sql server database from .bak file Using T-SQL
You may also use T-SQL query to restore a database from the .bak file by following these steps:
Step 1: Start SSMS , and then connect to an instance of SQL Server.
Step 2: Click the New Query option.
Step 3: In the Query Editor window, run any of the following RESTORE statement:
- For restoring the entire database from the .bak file, use the following command:
Make sure to replace ‘DBName’ with the name of the database that you wish to restore. Also, replace ‘BackupFilePath’ with the path of your db backup file, and ‘BackupFileName’ with the name of your .bak file.
- For restoring a specific file from the .bak file, use a command that resembles the following:
Replace ‘FileName’ with the name of the file you want to restore.
Method 3 – Restore-SQL Database with Replace PowerShell
You can also restore a SQL db by using the Restore-SqlDatabase cmdlet. The cmdlet performs restore operations including full database restores, transaction log, and database file restores on a SQL Server db.
| IMPORTANT! If you have an updated full database backup file, you can efficiently restore the database by using the above methods. But, you cannot perform selective database recovery from the backup file. For instance, you can recover a single table (if needed) from backup. |
What Else You Can Do to Restore SQL Server Database?
If your SQL database is corrupt and you don’t have a complete backup copy to restore the db, using Stellar repair for MSSQL- Technician can help. It’s a comprehensive software that comprises tools to help DBAs solve multiple problems. With the backup extractor tool available in the technician, you can extract data from a corrupted backup file (.bak). You can use the SQL repair tool to fix severely corrupted SQL data files (.mdf and .ndf) and recover all the database objects. In addition, the password recovery tool of the technician can help reset lost/forgotten password of a db file.
Conclusion
Maintaining databases in SQL Server and keeping them up and running is the foremost priority of DBAs. Thus, if there is any conflict, it should be resolved quickly by performing SQL server database restoration process using the last known good backup copy.
Understanding the T-SQL commands or steps to use SSMS to restore database in SQL Server from .bak file can help you carry out SQL database restoration efficiently. If the backup file is not available or you are unable to restore the db from the file, using the backup extractor tool of Stellar Repair for MS SQL- Technician software can help. The tool helps extract and recover data from a corrupted .bak file. You can also use other tools available in the technician to resolve database corruption and password recovery issues.
Q. Does the Stellar Repair for MS SQL software recover data from a corrupt .bak file?
A. No, the software helps repair a corrupt SQL database (MDF or NDF) file and restores data in a new file without making any changes to the original file.
You can try using the backup extractor tool available in Stellar Repair for MS SQL -Technician to extract data from a corrupted backup (.bak) file.
Q. Is it possible to restore a differential backup without having a full database backup?
A. No, it is not possible to restore SQL database from differential backup if you haven’t previously taken a full database backup. This is because a differential backup relies on the last full data backup, and saves only the data that has changed since the last full backup was performed.
Q. Is it possible to open a corrupt .bak file in SQL Server using command line?
A. We don’t recommend the use of command line as there are chances of data loss. You can try Stellar Repair for MS SQL-Technician to open a corrupt .bak file in SQL Server.
How to open a .bak file
You probably guessed up until this point, if you didn’t already know, a .bak file is a backup file that contains the details or information related to a software system, the contents of a database, or another file.
The most common .bak files nowadays are database backups of SQL Server databases, but there are still other programs (like SqlBak) that might create files with this extension. You can find a more detailed list here.
Because this extension is used for backup files of any type there is no standardized format for creating .bak files, thus each program that creates .bak files can decide on how to format them.
This also means that unlike other types of files, like .zip or .rar which have a standard after which they are created and how they are un-archived, there is no standard program for extracting/importing information from a .bak file.
But there is still good news, as you don’t need to download a special program to view the contents of a .bak file, but the bad news is that there is a high chance that you will have to open each .bak file differently.
How to open a .bak file?
As we have already said, .bak files can be created by multiple programs and, if you don’t know anything about the file’s history than you will have to go through a few more steps in order to extract information from the file.
If you do know a few details about the file’s history, like what program it has been exported from, just go to that program and see if there is a restore option for your file.
But as this is a blog related to SQL Server and its backups the chances are higher that you want to open (or restore) the .bak file. Thus we will continue with how to open a backup file of a database.
In order to see the information in a backup of a database, you will have to restore it first. Unfortunately, there is no other way of seeing the information as it is formatted. You can try to open it with text editors like Notepad or similar software but you will just get a mess of characters displayed.
How to restore a .bak file?
As we said, there is no other way of opening and reading the contents of a SQL Server .bak file other than restoring it.
The process of restoring the backup file will create the table structures and insert the data, depending on the type of backup file that it is. To restore a .bak file using SQL Server Management Studio (SSMS) you must do the following steps.
On the database you want to restore, right-click it then go to Tasks -> Restore -> Database, as can be seen below.
Then check the radio button From device and click on the button to browse for the location of the .bak file.
You will get to a screen like the one below where you will have to click on Add in order to choose the .bak file.
The next screen that appears will be similar to the one below, where you will have to go to the folder that contains your .bak file and select it for restoring.
Once you have selected the file, click OK and then OK again until you get back to this screen where you will have to select the backups you want to restore.
You will have to tick both the FULL backup type and the Transaction log in order for the restore process to succeed.
Once you select them you press OK and you should pretty soon (depending on your backup’s size) get a message similar to this.
And this is all you have to do. Now your database should be online and you will be able to access the data inside it.
How to restore a .bak file MUCH FASTER?
There is a simpler option to avoid doing all this through SSMS, or even if you don’t have SSMS available. Just download SQLBackupAndFTP and you can restore your .bak file MUCH FASTER.
All you need to do is to create a backup job using the following instructions. It’s as easy as 1-2-3!
Import .bak file to a database in SQL server
How can I import this date to a database in SQL Server?
9 Answers 9
On SQL Server Management Studio
- Right click Databases on left pane (Object Explorer)
- Click Restore Database.
- Choose Device, click . , and add your .bak file
- Click OK, then OK again
This will show you a list of database files contained in DB.bak:
You will need the logical names from that list for the MOVE operation in the second step:
and you have to move appropriate mdf,ndf & ldf files using
You can simply restore these database backup files using native SQL Server methods, or you can use ApexSQL Restore tool to quickly virtually attach the files and access them as fully restored databases.
Disclaimer: I work as a Product Support Engineer at ApexSQL
Instead of choosing Restore Database. select Restore Files and Filegroups.
Then enter a database name, select your .bak file path as the source, check the restore checkbox, and click Ok. If the .bak file is valid, it will work.
(The SQL Server restore option names are not intuitive for what should a very simple task.)
On Microsoft SQL Server Management Studio 2019:
On Restore Database window:
Choose Add and pick target file
OK to confirm restore
- Connect to a server you want to store your DB
- Right-click Database
- Click Restore
- Choose the Device radio button under the source section
- Click Add.
- Navigate to the path where your .bak file is stored, select it and click OK
- Enter the destination of your DB
- Enter the name by which you want to store your DB
- Click OK
Although it is much easier to restore database using SSMS as stated in many answers. You can also restore Database using .bak with SQL server query, for example
In above Query you need to keep in mind about .mdf/.ldf file location. You might get error
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\AdventureWorks.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.SmoExtended)
So you need to run Query as below
Once you will run above Query you will get location of mdf/ldf use it Restore database using query
