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.
.jpg)
Figure 2 - Locate SQL Database Backup File
- From 'Select backup devices' window, choose File as backup media, and then click Add.
.jpg)
Figure 3 – Select Add to Navigate to .BAK File
- Locate and select the .BAK file you want to restore, and then click OK.
.jpg)
Figure 4 – Select the Backup File
- Again click OK to return to the 'Restore Database' window.
.jpg)
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.
| Note: Choosing the 'Overwrite the existing database (WITH REPLACE)' option will overwrite your existing db. If you want to prevent this, create a new database and move the physical file to a new location. |
.jpg)
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)'..
| Note: If you are doing differential or log restore after full restore you cannot leave your database in recovered state. You will have to leave it in non-recovered state ready for more backups to be applied, i.e. Restore Database with norecovery. |
.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:
| RESTORE DATABASE FROM DISK = '\' GO |
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:
| RESTORE DATABASE FILE = FROM DISK = '\' GO |
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.
Read this: How to restore the SQL Server database with Stellar Repair for MS SQL Software?
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.
9 min read
-1112.jpg)
