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

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.

• Method 1. Restore SQL database from .bak file in SSMS
• Method 2. Restore SQL database from .bak file using T-SQL statement
• Method 3. Restore SQL database from .bak file via command line
• Easier alternative to restore SQL database from backup

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

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.

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.

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:

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:

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

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:

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

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.

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

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:

In my case, it should be:

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:

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

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:

Or restore the database WITH REPLACE or WITH STOPAT:

Easier alternative to restore SQL database from backup

The advantage of restoring SQL database from bak file is mostly the flexibility. For example, you can backup SQL database and restore to another server. But the above methods are either cumbersome or require some SQL knowledge. As a supplement, I’d like to introduce AOMEI Centralized Backupper Database, which can centrally backup and restore SQL Server database among all the network connected Windows PCs or servers.

This software works with Windows 11/10/ 8.1/8/7/Vista/XP, Windows Server/2003/2008 (R2)/2012 (R2)/2016, Windows SBS 2003/2008/2011, Windows Home Server 2011 (32/64-bit).

Its SQL Server Backup feature supports SQL Server 2005 to 2019, 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:

🔹How to backup SQL Server databases:

1. Install the software properly and gain controls over those client computers you want to manage (you can control you own machine, of course).

Then choose SQL Server Backup in Home tab.

2. In Step 1, click on + Add Computers to detect the computers (with SQL database) under your control, then choose one to backup.

3. In Step 2. Click + Add to detect all the instances on the computer, and select one or multiple databases for backup. You can even select the entire instance.

4. In Step 3. Click Add Storage to specify a share or NAS device as the storage end. Then Start Backup.

🔹How to restore SQL database from backup:

1. Once a backup task is created, it will show in the Tasks tab. Find the one you want, click Advanced at the its top-right corner and choose Restore.

2. Select the computer and its databases you want to restore. If you’ve backed them up several times (full or differential), you can also select the specific backup to restore from at the upper right.

3. You can restore these databases to original location or new location. With the second option, you can directly restore databases to another SQL Server. Then, click Start Restore to execute it.

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 Centralized Backupper Database Edition to manage SQL Server backup and restore more intuitively.

It works for all the PCs and servers within LAN. And the data types it supports are not only SQL databases, but also computer files, operating systems, partitions and disks. With it, you can centrally backup multiple servers or PCs, and restore from any backup task as you need.