How to Restore SQL Database from Bak File (3 Methods)

This post will guide you on how to restore SQL Server databases from bak file. Follow this detailed tutorial to restore database from bak file using SQL server Management Studio, T-SQL script, PowerShell command. Also we will introduce an easier way to backup and restore SQL databases.

Delia

By Delia / Updated on January 11, 2024

Share this: instagram reddit

How to restore SQL database from .bak file

After backing up SQL Server successfully, you will get a .bak file that can be used to restore database from backup via SSMS, Transact-SQL, command line, etc.

In this post, I will introduce these frequently used recovery approaches, and an easy alternative to restore SQL database from backup easily. Keep reading and pick the way you like.

📢Note: All the methods below only support restoring database from lower version to the same or higher version , like from SQL Server 2012 to 2016. If you want to migrate database to a lower version, please refer to this guide.

Method 1. Restore SQL database from .bak file in SSMS

As with the backup, SSMS also includes a restore wizard to guide you through the process intuitively. To use it, please connect to the instance you want to restore database to.

1. Right-click Databases and choose Restore Database… in the menu.

restore database

2. In the popping out window, choose the “Source” as Device, and click the icon to Add the bak file. If you cannot find it, please locate to its save path and enter its full name (including .bak) to specify it.

restore from device

3. The backup information will show in the Restore Database window if there’s no problem, then you can click OK to restore SQL database from the bak file.

restore successfully

Notes:✎...
If the restoration failed because “The operating system returned the error '5(Access is denied)'…”, please switch to Files tab, check Relocate all files to folder and try it again.
If you find your SQL database stuck in restoring state, the reasons could be you didn't stop restore process correctly, you don't have enough disk space, others are using this database, or users didn't close Query window after use, etc. Among them, the most common reason is that you wrongly used No Recovery option to restore database.
If the target instance does not contain the database you want to restore, a new database (with the same name) will be auto created to receive all the data in the bak file. So please don’t create and name an empty database as the target, otherwise you will receive error 3154 saying "the backup set holds a backup of a database other than the existing database".

Method 2. Restore SQL database from .bak file using T-SQL statement

In addition to following the wizard, you can also restore database in SQL Server using query. It’s actually an easier way as long as you get to know the T-SQL statements.

1. Click on New Query to open a SQLQuery window. Type the restore command in it:

RESTORE DATABASE databasename FROM DISK = 'filepath\filename.bak'

For example, if I want to restore the SQL database “example” from a full backup “example.bak” in D:\Backup, then the command will be:

RESTORE DATABASE example FROM DISK = 'D:\Backup\example.bak'

2. Hit the Execute button to run the command, you will see the backup status in the Messages below.

t-sql restore full backup

How to restore database from a differential backup

To restore from a differential backup, you need to restore the last full backup WITH NORECOVERY in advance, so the command will be like:

RESTORE DATABASE databasename FROM DISK = 'filepath\filename.bak' WITH NORECOVERY
GO
RESTORE DATABASE databasename FROM DISK = 'filepath\filename.bak' WITH RECOVERY
GO

Note: The first bak file should be the full backup file, and the second one should be the differential backup file.

t-sql restore from differential backup

Issue: The tail of the log for the database has not been backed up

During the restoration, you may receive an error saying “The tail of the log for the database has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.”

This is because the backup file that you are trying to restore is older than the target database. To resolve it, you can follow the tips mentioned in the message:

1. Backup the transaction log WITH NORECOVERY beforehand, and then try restoring the bak file again.

BACKUP LOG databasename TO DISK = 'filepath\filename.trn' WITH NORECOVERY

2. Restore the database using the WITH REPLACE or WITH STOPAT clause.

RESTORE DATABASE databasename FROM DISK = 'filepath\filename.bak' WITH REPLACE

Method 3. Restore SQL database from bak file via command line

Alternatively, you can use command line to restore SQL database from a backup without launching SSMS - this requires SQLCMD, which is shipped with SQL Server in 2014 and lower versions. If you are running higher versions, please download it here.

In a previous article, I’ve explained how to backup SQL Server database with command line, you can hit the link to get more details. Here I'll focus on how to restore bak file.

1. Press Win + R and type “cmd” in the input box. Click OK to open Command Prompt.

2. Then, use this command with your own server name and filepath:

SqlCmd -E -S servername -Q “RESTORE DATABASE databasename FROM DISK='filepath\filename.bak'”

In my case, it should be:

SqlCmd -E -S .\MSSQLSERVER_01 -Q “RESTORE DATABASE databasename FROM DISK='D:\Backup\example.bak'”

cmd restore from full backup

And if you want to restore a differential backup using command line, you can first restore the full backup WITH NORECOVERY, and then restore the differential backup WITH RECOVERY:

SqlCmd -E -S .\MSSQLSERVER_01 -Q “RESTORE DATABASE databasename FROM DISK='filepath\filename.bak' WITH NORECOVERY”
SqlCmd -E -S .\MSSQLSERVER_01 -Q “RESTORE DATABASE databasename FROM DISK=' filepath\filename.bak' WITH RECOVERY”

Note: The first bak file should be the full backup file, and the second one should be the differential backup file.

cmd restore from differential backup

Similar to the previous method, if you encounter “The tail of the log for the database has not been backed up” error, you can either BACKUP LOG WITH NORECOVERY in advance:

SqlCmd -E -S servername -Q “BACKUP LOG databasename TO DISK='filepath\filename.trn' WITH NORECOVERY”

Or restore the database WITH REPLACE or WITH STOPAT:

SqlCmd -E -S .\MSSQLSERVER_01 -Q “RESTORE DATABASE databasename FROM DISK='filepath\filename.bak' WITH REPLACE”

That's it, the recovery issue should be resolved.

Easier alternative to restore SQL database from backup

It's flexible to restore SQL database from bak file. You can backup database and restore to another server, for example. But the above methods are either cumbersome or require some T-SQL knowledge. As a supplement, I’d like to introduce AOMEI Cyber Backup, which can centrally backup and restore SQL Server database among all the network connected Windows PCs or servers.

This software works with all the Windows OS above (and including) Windows 7 and Windows Server 2008 R2. Its SQL Server Backup feature supports SQL Server 2005 to 2022, and enables you to back up or restore multiple databases at once. The whole operation is done through simple clicks with no expertise required.

Here’s a 30-day free trial:

Download Free TrialMicrosoft SQL Server 2005-2022
Centralized and secure SQL backup

🔹How to backup SQL Server databases:

1. Run AOMEI Cyber Backup on the server machine, click Source Device -> Microsoft SQL -> Add Microsoft SQL.

Microsoft SQL

Download proxy program and install it on the device with SQL Server installed. Then, click Already installed proxy and select the device with SQL installed.

Next, click icon -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

Add Device

2. Click Backup Task on the left menu bar, select Backup Task -> Create New Task to open the task creating page. Choose backup type as Microsoft SQL Backup, and set the Task Name as you like.

Backup Type

3. Click on Device Name to specify the SQL instances and databases you need to back up. You can select one or multiple database flexibly.

Select Database

4. Select Target to save backups. You can specify a local or network path.

Choose Target

5. Before Start Backup, you can also set up a Schedule to run the SQL database backup automatically. Meanwhile, you can select the backup method as Full, Incremental or Differential Backup.

✍Schedule Backup helps you backup your database automatically. The schedule setting is enabled by default. You can edit or disable it on your needs.
✍Backup Cleanup is an advanced feature that can automatically remove history backup versions based on a rule to save your storage space.
✍Email Notification enables you to receive email notifications when the task is abnormal or successful.

A completed task will be listed in the Backup Task tab. Then you can edit or restore from SQL database from it when you need.

🔹How to restore SQL database from backup:

1. Click Backup Task on the left menu bar, locate the task you want to restore, and click icon -> Restore.

Restore

2. Select a backup version that you want to restore.

Select Content

3. Select the target location that you want to restore to. If you restore to original location, you don’t need to select target. Restore to original location is selected by default and you can directly click Start Restore.

Restore to Original Location

If you want to Restore to new location, you need to select target and specify the name of the new database. You can also modify the storage location.

Restore to New Location

Overwrite the database with the same name: If you tick this option, the backed up databases will overwrite the target databases of the same names. If you leave it unchecked, the target databases with same names will be omitted during restoring.

4. After all the settings, click Start Restore to begin the restore progress, and wait patiently for it to complete. You can click on the icon button to see restore details or cancel it.

Start Restore

When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio.

✍Notes:

  • "Restore to original location" must confirm the original location exist. Or else, you can only select "Restore to new location".
  • "Restore to original location" will overwrite or delete the original database data, if the original database has important data, it is recommended to choose "Restore to new location".

Conclusion

3 methods are provided in this article to restore SQL database from a bak file. In fact, you can also use effective third party software - AOMEI Cyber Backup to manage SQL Server backup and restore more intuitively.

Delia
Delia · Editor
Delia owns extensive experience in writing technology-related blog posts, and has been a part of AOMEI since 2020 to provide expertise in data security and disaster recovery. She works with Windows operating systems, SQL databases, and virtualization platforms such as VMware and Hyper-V, specializing in troubleshooting and advising on data protection and migration.