Overview
In most of the cases, users create a backup file of their database on their machine. This help the users to perform recovery at the time of any disaster, or manage large amount of database onto the SQL server. However, users generally knows the way to create a backup of a database on their machine, but are unaware from the way to restore database from .bak file on SQL Server. Therefore, this blog will help such users to restore database in SQL server either by using query or SSMS.
Steps to Recover SQL Server Database From .bak File Using SSMS
In order to restore SQL Server database from .bak file, it is mandatory to have a backup file on computer system. In addition, go through following set of procedure for recovering SQL server database from a backup file:
- Launch the SQL Server Management Studio on your machine. Then, from the left-hand side menu of the management screen, right click on Databases option and then select Restore Database option from it.
- A Restore Database Window will appear. From this Window, check the checkbox of Device option
- Click on the Browse button from the restoration window
- Now a backup device window will appear in front of you. In this window, you have to choose File as backup media type and then click on Add button
- Now navigate to the location where the backup file is stored. Click on the file having .bak file extension for selecting it and then click on OK button
- Analyze the selected database file from middle pane area of backup device window and then click on OK to continue further
- Click on OK button of the Restore Database window. This will start the procedure of recovering the server database from a backup file
- After the completion of restoration procedure, a message window will appear that indicates that the complete data is now recovered from the BAK file. Click on OK button to terminate the current settings window
- Now either press F5 from your keyboard or click on Refresh button of SQL server Management Studio. This will display the database restored from the backup file
Finally, you will be able to see database of your backup file, on left-hand side panel of management studio. You can click on expand button to see further tables of the restored database.
Steps To Restore Database from Backup In SQL Server Using T-SQL
In this case, if the database already exist, then data of existing database will overwrite the files or tables with new one. If database exist, then follow the following commands:
- Launch a SQL query window on your server
- Set the database of the server in Single User Mode by executing following command:
ALTER DATABASE < Database Name > SET SINGLE_USER with Rollback IMMEDIATE - Run the following command for restoring the database from backup file:
RESTORE DATABASE < Database Name > FROM DISK = ‘< Location where BAK file is stored >’ WITH REPLACE, STATS = 5
If the database does not exist, then execute following command in query window of the server:
RESTORE DATABASE AdventureWorks FROM DISK = ‘< Location of BAK file with file name >’
Observational Verdict
In this blog, users can find strategy to restore database in SQL server from backup file using query or SSMS. In addition, it is also applicable to all other versions of the SQL server. The most easiest way is to recover SQL server database from .bak file using SQL server management studio .It is quite easy to access, in comparison to query procedure. This step guide is sufficient to recover SQL server database from backup file and thus no need of any technical expert to perform the same. However, both the procedures are foolproof and could be accessible by a novice users also and it is recommended to validate your backup before going to restore it. Because, Sometimes, it may not be possible to restore a backup due to a corrupt .bak file. In this worst scenario one can try page checksums or use third party SQL Backup Recovery Tool to overcome with this situation.
