Summary: The article provides steps to restore the database in SQL Server from .bak file. Try to restore the backup file by using SQL Server Management Studio (SSMS), implementing Transact-SQL (T-SQL) query, or via PowerShell command. If you find that backup is not available or is out of date, or the database is damaged, then use some professional software to restore the database instantly.
Various SQL Server Database Administrators (DBAs) come across problems of errors such as malicious software attack, abrupt termination, database connection or transient errors, the issue with drivers, and so on. Such errors can make the database unusable. By rebooting the SQL server some of the problems can be sorted out, using a backup (.bak) file is the most practical approach to restore the SQL database.
Restore Database in SQL Server from.BAK File
When we say Restore Database in SQL Server from.BAK File, means performing Full, Differential, File, or Filegroup database backup using SQL Server Management Studio (SSMS), T-SQL, or by using PowerShell.
Things you should know before commencing Backup Restoration
Following are the things you should know before backup restoration:
Make sure that you close all the active connections before restoring the SQL Server database from.BAK file. This will help to prevent the restore process from failing because of active database connections.
The Backups which are created on recent SQL Server versions can’t be restored to prior versions of SQL Server.
It is important to back up the active transaction log, before you commence the full database backup. In case, if the active transaction log (also known as the tail of the log) becomes unavailable, all transactions in the log will be lost.
When you feel the need to restore a .db from another server, you need to manage metadata to make the .db available on another server.
Methods to Restore SQL Server Database from.BAK File
#1: Restore SQL Database using SSMS
If SSMS is installed on your system, follow the steps to restore your SQL database from the backup (.bak) file. To do this, follow the below steps:
Step 1: First, Open SSMS and connect to an instance of SQL Server.
Step 2: After this, go to Object Explorer and click the Server Name so that you can expand the Server tree.
Step 3: In the next step, navigate to Databases and open the database which you want to restore in SQL Server.
Step 4: Make sure that you Right-click the Database, and again click Restore Database.
Step 5: You will find the Restore Database window on screen. From the General page available under Source section, choose any of these options:
a. Select the Database option, and then choose the database which you want to restore from the drop-down list.
b. Select the Device option, and click the ellipses (…) to find the backup file.
- From ‘Select backup devices’ window, choose File as backup media, and then click Add.
- Locate and choose the .BAK file which you want to restore and click OK.
- Make sure that you again click OK to return to the Restore Database window.
- Go to the ‘Select backup devices’ window and select File as backup media and click Add.
- Locate and select the .BAK file you want to restore, and then click OK.
Click OK to return to the ‘Restore Database’ window.
Step 6: The name of the database that has to be restored will appear in the Database box under Destination section. The information of the chosen backup file will also be added in the Backup sets to restore section.
Here, all the information need to restore the database from the backup file which has been entered. Click ‘OK’ button to restore the database. However, SQL .bak files store info about the location of data files, and if any problem appears with those files, like “conflicting data file names” or “the destination directory does not exist” may cause the error.
Such issues appear while restoring the SQL database from a.BAK file created on some other SQL Server installation. By changing the file settings from the Options page will help to overcome the matter. Follow the below steps to do this:
a. Choose Options under Select a Page.
b.Once you move on the Options page, do the following:
- Under the Restore options section, select the ‘Overwrite the existing database (WITH REPLACE)’ option.
- Go to the Recovery State section and select ‘Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)”.
Step 7: To carry on with the restore process, press the OK button.
Step 8: The window with restore progress is displayed.
Step 9: After you find the message ‘restore of database completed successfully’, click ‘OK’
# 2: Restore SQL server database from .bak file Using T-SQL
If you have restored the database in SQL Server from. bak file with the help of T-SQL query, then follow the below-mentioned steps:
Step 1: Start SSMS and connect to an instance of SQL Server.
Step 2: Next, click the New Query option.
Step 3: Go to the Query Editor window and run any of the following RESTORE statement:
- Use the following command to restore the entire database from the .bak file:
RESTORE DATABASE FROM DISK = ‘\’ GO
Ensure replacing ‘DBName’ with the name of the database which you want to restore. In addition to this, also replace ‘BackupFilePath’ with the path of your db backup file, and ‘BackupFileName’ with the name of your .bak file.
- To restore the specific file from the .bak file, use the following command:
RESTORE DATABASE FILE = FROM DISK = ‘\’ GO
Replace the ‘FileName’ with the name of the file which you want to restore.
# 3 – Restore-SQL Database with Replace PowerShell
Another way out you can try is to restore a SQL db by using the Restore-SQL database cmdlet. The cmdlet performs the restore operations comprised of full database restores, transaction log, and database file restores on a SQL Server db.
Note: If you are possessed with the updated full database backup file, you can restore the database by using the above-given methods. However, you cannot perform the selective database recovery from the backup file. Suppose, you want to recover the single table (if needed) from backup, you can do so.
Conclusion:
It is always crucial to maintain databases in SQL Server and keep them up and running. And, in case of any trouble, resolve the matter by commencing the SQL server database restoration process by utilizing the last known good backup copy. By understanding the T-SQL commands or steps to use SSMS to restore the database in SQL Server from .bak file may help you to perform SQL database restore easily.