How to Restore Database in SQL Server from .Bak File ?

SQL database files (MDF/NDF) are prone to corruption, integrity issues, and consistency errors. If your database is corrupt or damaged, the most reliable and the easiest option is to restore it from backup. Below, we will discuss different methods to restore the SQL database from a backup (.bak) file.

Prerequisites for Restoring Backup File in SQL Server

Before restoring SQL Server database, check and ensure these requirements are met to prevent failure and errors.

Check System Disk

Make sure your system’s disk has sufficient free space available, i.e., equal to the original database size. If you try to restore backup on the drive with insufficient space, SQL Server will block the operation and display an allocation error (Error 3257) before the process begins. According to Microsoft guide, you can use the use the RESTORE FILEISTONLY command to view the exact space required by each database file within the backup set.  Here’s how to run this command:

This command also helps you to know the logical names of the data and log files inside the .bak file.

Check Permissions

Ensure your SQL account has the required permissions on the target instance. According to Microsoft documentation, if you are restoring a backup to create a new database, you need CREATE DATABASE permissions. If you are overwriting an existing database, you must be the database owner (dbo) or a member of the sysadmin or dbcreator server roles.

To verify your server roles, use the IS_SRVROLEMEMBER command (see the below example):

To check database-level roles for a specific database, run the IS_ROLEMEMBER (Transact-SQL) command as given below:

If the column displays the value 1, it means you have server-level rights and you can proceed with backup restore process. If it returns 0, it means you lack such permissions.

Check and Disconnect Active Connections

If some users are connected to the database, the restore process will fail with the SQL database in use error and terminate abnormally. To prevent this, you must get exclusive access to the database by disconnecting all active users and set the database to single-user mode. For this, you can run the below command:

Run RESTORE VERIFYONLY Command 

Use this command to check the integrity of your .bak file and confirm it is free of corruption before restoring.

If the command displays the message “The backup set on file 1 is valid”, it means your backup file is healthy.

Methods to Restore SQL Server Database from .BAK File

Here are some methods you can use to restore database files (MDF/NDF) from backup.

Method 1: Restore SQL Database from Backup using SSMS

SQL Server Management Studio (SSMS) is a comprehensive  integrated environment designed for managing SQL databases. It provides a user-friendly graphical user interface to simplify the tasks.  Follow these simple steps to restore SQL database from the .BAK file using SSMS:

Step 1: Open SSMS and connect to the instance of SQL Server (in which your database is saved).

Step 2: 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.

Step 5: The Restore Database window is displayed. On General page, under Source section, choose the Database option and then select the database you want to restore from the dropdown list. The Destination fields will populate automatically. If required, you can manually modify the recovery point using the Timeline option. Click OK.

Step 6: Under the Backup sets to restore grid, ensure that the checkbox next to the Full backup component is selected. You can also look at the Name and Date columns within the grid to cross check that you are restoring the correct backup version.

Step 7: Select the advanced options from the Options page. For this, select Options under Select a Page. 

Step 8: On the Options page, under the Restore options section, select one of the following options as per your requirement:

• Overwrite the existing database (WITH REPLACE)' option.
• Preserve the replication settings.
• Restore access to the restored database.

NOTE: Choosing the 'Overwrite the existing database (WITH REPLACE)' option will overwrite your existing database. If you don’t want to overwrite the database, create a new database and move the physical file to a new location.

Step 9: Under the Recovery State section, choose RESTORE WITH RECOVERY, RESTORE WITH NORECOVERY, or RESTORE WITH STANDBY.

Step 10: Click on the OK button. A window with restore progress is displayed.

Step 11: Click OK when ‘The restore of database completed successfully' message pops up. 

Method 2: Restore SQL Database from .BAK file using T-SQL

You can also restore your backup file in the SQL Server Management Studio (SSMS) by using T-SQL queries. This is highly efficient for restoring large database files. Here is the step-by-step process:

Note: To restore backup using T-SQL commands, you should know the logical names of the database files inside the backup and the backup position number.

Step 1: Start SSMS and then connect to your instance of SQL Server.

Step 2: Click the New Query option.

Step 3: First, run these commands in the Query Editor window to find the exact parameters needed to restore the backup file:

• RESTORE HEADERONLY command: To see the number of backup sets (transaction or differential) exist inside the .bak file and check their position in the Position column.

• FILELISTONLY command: To find the logical name of the log and data files.

NOTE: Do not skip these diagnostic commands when using a backup from another server. If the backup restore script does not match the internal metadata, you may encounter errors like “File cannot be restored" or a "Backup set holds a backup of a database other than the existing."

Step 4: Now, use the gathered information to run the following command for restoring the entire database from the .bak file:

Method 3: Restore SQL Database with Windows PowerShell

You can also use the Restore-SqlDatabase command in Windows PowerShell to restore the SQL Server database. It supports full database restore, transaction log restore, and database file restore. Here’s the syntax to restore database from BAK file using the Windows PowerShell:

You can also restore SQL Server database from Command Line. Open the CMD window and run this query:

Method 4: Restore Database using Azure Data Studio

Azure Data Studio (formerly known as SQL Operations Studio) is a cross-platform desktop environment for the developers of both on-premises SQL Server and cloud. It is compatible with Windows, macOS, and Linux. It is an alternative to SSMS that allows you to quickly perform most administrative SQL Server tasks via the integrated terminal using PowerShell or sqlcmd. Also, it helps you quickly chart and visualize result sets. However, it is retired and no longer receives updates and fixes.

You can use Azure Data Studio’s alternative - the MSSQL extension for Visual Studio Code. It supports schema management, query execution, and other database operations. You can even restore databases from existing backup sets or restore a backup (.bak) file using the MSSQL extension for Visual Studio Code. To do so, follow the steps below:

• Download, install, and launch Visual Studio Code.

• Open the MSSQL extension and in the Connections view, expand your active SQL Server instance.
• Expand the Databases folder.
• Right-click any database (or system database) and select Restore Database.

• Under Restore –localhost,1434, select the source database and one of the following locations from where you want to restore:

• Database
• Backup File
• URL

• Select the Target Database, check the available Backup sets to restore, and click Restore.

Understanding RECOVERY, NORECOVERY, and STANDBY 

SQL Server offers options to control the state of database after backup restore. These are:

RESTORE WITH RECOVERY

When you use the RESTORE WITH RECOVERY option, it indicates no more restores are required and the state of database changes to online after the restore operation. If you have not specified any of the recovery options, like STANDBY, NORECOVERY, etc., then RECOVERY is the default.

RESTORE WITH NORECOVERY

You can select the WITH NORECOVERY option when you want to continue restoring additional backup files, like transactional or differential. It changes the database to restoring state until it’s recovered. Whenever you need to perform a multi-step restore sequence, you need to use WITH NORECOVERY option on all statements until the desired recovery point is reached and then use RESTORE WITH RECOVERY statement for recovery only.

RESTORE WITH STANDBY

It leaves the database in read-only mode. It allows you to check your data between transaction logs restore, before the final recovery is finished. It is highly useful to verify that the transaction logs are applying correctly. You can use this option when you need to recover data from a precise moment but do not know exactly when the modification or corruption occurred.

How to Restore Differential and Transactional Backups in SQL Server?

Differential backups contain only changes made since the last full backup. To restore differential backup in SQL Server, the options are exactly the same but you require a healthy full backup. First, you need to do a full database restore with the NORECOVERY option to allow you to restore the differential backup using WITH RECOVERY.

Transaction log backup stores all the transactions occurred since the last transaction backup. To restore this type of backup, you require to restore last backup WITH NORECOVERY and then restore all transaction log backups WITH NORECOVERY except the last one, which is restored WITH RECOVERY.

You can use transaction log backup to restore the database to a specific point in time. For this, SQL Server uses the log sequence numbers (LSNs) to ensure that the restore takes place in a correct sequence. You can view the LSNs using the RESTORE HEADERONLY command.

Here’s how to restore a database using differential and transaction log backups:

Restoring to a Different Server or Renaming the Database 

To restore SQL Server database to a new location, you can use SQL Server Management Studio (SSMS) or the Move option with the RESTORE DATABASE statement. This allows you to relocate a database to a new directory or create a copy of a database on either the same server instance or a completely different instance.

Read our complete step-by-step guide: How to Restore SQL Database with a Different Name.

SQL Backup Restore: PowerShell vs SSMS vs T-SQL vs Command Prompt vs Azure Data Studio

The table below compares the above-mentioned SQL database backup restore methods based on different criteria:

Common SQL Backup Restore Errors and How to Fix them 

Here are some common issues and error messages you may encounter while restoring backup file in SQL Server:

• Error 3183: Corrupt SQL Backup File
• SQL Database Restore Failed, Database in Use
• Database cannot be opened: It is in the middle of a restore
• The database cannot be recovered because the log was not restored
• SQL Backup Restore Error 3013
• SQL Database Error 3241: Restore Headeronly

Such errors usually occurs due to corruption in backup file. However, there can be multiple other reasons like active connection or version mismatches.

What if your SQL Database Backup File is Corrupted?

The above restore methods require a healthy backup file to work. In case the SQL backup is corrupted or it’s not available, there is not much you can do using the native tools. In such a situation, you need to rely on a SQL database repair software, like Stellar Repair for MS SQL Technician. The software contains a utility, named Stellar Backup Extractor for MS SQL, specifically designed to recover data from corrupt backup files. It can recover all the objects from database, including tables, indexes, collation, and even deleted records. It then saves the recovered data in a new database (MDF) file.

The software supports all SQL backup types, including Full Backup, Differential Backup, and Transaction Log Backup. It can also repair corrupt database (MDF/NDF) files without any file size limitations. The tool supports recovery from compressed backup files. It is compatible with MS SQL 2025, 2022, 2019, and lower versions.

Read this: How to recover the SQL database from corrupt backup file using Stellar Repair for MSSQL.

Conclusion

If your SQL database is corrupt, you can restore the backup file. In this article, we have discussed how to restore .bak file in SQL Server. You can choose the restore method based on your setup. Make sure you have correctly implemented database states while restoring and used the MOVE clause correctly to prevent backup restore errors. If the backup file itself is corrupted, use Stellar Repair for MS SQL Technician to recover the data.