File Repair

How to Restore Database in SQL Server from .Bak File Step By Step

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 Toolkit for MS SQL software can come in handy. The toolkit 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.

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.

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

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.

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.

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 Toolkit for MS SQL can help. It’s a comprehensive software that comprises tools to help DBAs solve multiple problems. With the backup extractor tool available in the toolkit, 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 toolkit 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 Toolkit for MS SQL software can help. The tool helps extract and recover data from a corrupted .bak file. You can also use other tools available in the toolkit to resolve database corruption and password recovery issues.

FAQs 

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 Toolkit for MS SQL 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 Toolkit for MS SQL to open a corrupt .bak file in SQL Server.

88% of people found this article helpful