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

Author: Amelia Williams | Updated on June 24, 2020 to Resource

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).

This article will describe the step-by-step process to restore database in SQL Server 2014 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, use a SQL Database repair software to save time and efforts in fixing the db.

Restore Database in SQL Server 2014 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. This is because the restore process may fail if there are 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 – Use SSMS

Step 1: Open SSMS and connect to an instance of SQL Server 2014.

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 2014.

Step 4: Right-click the Database, select Tasks > Restore > Database.

Figure 1 - Restore Database Option

Step 5: When the Restore Database window is displayed, do the following:

Figure 2 - Restore Database Window

    a. Under Source for Restore section, select the From device option. 

Figure 3 - Source for Restore

b. Click the browse button next to 'From device' to open Select backup devices window.

c. In the window that appears, click Add.

Figure 4 - Specify Backup Window

d. In the dialog box that pops-up, locate and select the .BAK file you want to restore, and then click OK.

Figure 4 - Specify Backup Window

    d. In the dialog box that pops-up, locate and select the .BAK file you want to restore, and then click OK.

Figure 5 - Locate Backup File Window

    e. Click OK to return to the Database Restore window.

Figure 6 – Selected Backup File in Specify Backup Window

Step 6: The .BAK file will get listed on the Database restore window.

Figure 7 – Select a page in Restore Database Window

Step 7: In the left-side panel, select Options under Select a Page, and perform the following steps:

Figure 8 - Select Restore Database Options Page

    a. Under the Restore options section, select 'Overwrite the existing database (WITH REPLACE)' option.

Figure 9 - Restore Options Section

NOTE: Choosing '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. 

    b. 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 10 - Recovery state Options

Step 8: To proceed with the restore process, hit the OK button.

Step 9: The window with restore progress is displayed.

Figure 11 - Restore Process in Progress Window

Step 10: Click OK when the 'restore of database completed successfully' message pops up. 

Figure 12 - Database Restoration Completed Message

Method 2 – Restore Database by Using Query

If you are interested in knowing 'How to restore database in SQL Server from bak file using query', perform these steps:

Step 1: Start SSMS, and then connect to an instance of SQL Server 2014.

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

NOTE: 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 


NOTE: 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 MS SQL software can help. 

The software can repair severely corrupt SQL database .mdf or .ndf file. Also, it can help you perform selective recovery of database components like tables, triggers, keys, indexes, etc.

The software recovers the SQL Database in its original file format and saves the database at the preferred location. The user-friendly GUI makes it easy to operate the software, even for the first time software users.

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 2014 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, use Stellar SQL Database Repair software to resolve the issue. 

88% of people found this article helpful

 

Recent Articles