SQL Server | 4 Methods to Restore Database from Backup

In case of data corruption or loss, you can restore SQL Server database from backup file using SSMS, T-SQL, command line, etc. Here are detailed steps.


By Delia / Updated on June 13, 2023

Share this: instagram reddit

How to restore SQL Server database from backup?

If you have backed up your Microsoft SQL Server, you will get bak files which can be stored in any secure location and used to recover databases when needed.

In this article, I will present 3 methods to restore SQL Server database from backup. Pick the one you prefer.

Restore database in SSMS GUI

You can complete the operation in a graphic interface intuitively. It is suitable for novices, but only allows restoring one database from one backup file at a time.

Restore SQL database using T-SQL

This way is more powerful and flexible to restore SQL Server backups, but you need to learn basic T-SQL commands in advance. Careless or incorrect use of T-SQL statements may lead to failures or even more severe problems.

Restore SQL database using Windows command line

If you don't want to log into SSMS every time you backup or restore SQL database, you can use T-SQL commands in Command Prompt, which requires the support of SQLCMD.

Restore SQL database via professional backup tool

If you want to perform flexible operations such as restoring multiple SQL databases at once but do not want to use T-SQL scripts, you can also use specialist tool to do it intuitively.

SQL Server

Method 1. Restore database from backup in SSMS GUI

In Microsoft SQL Server, restoring database from backup via SSMS it the most intuitive way.

1. Launch SSMS and connect to your instance. Right-click Databases and choose Restore Databasesin the menu.

Restore Database

2. Choose Device as the source, then click on the ... icon to Add the backup file. If you can’t find it, try navigating to its storage path, and type the file name (with.bak) manually.

Add Backup File

3. Confirm the backup information, and click OK to execute the restoration.

Restore Successfully

Note: If the restore failed with “The operating system returned the error '5(Access is denied)'…”, you can switch to Files tab, tick Relocate all files to folder and try it again.

Method 2. Restore database using T-SQL commands

1. Connect to the instance you want to restore database to. Click New Query to open a new window, and enter the following T-SQL command:

RESTORE DATABASE DatabaseName FROM DISK = 'FilePath\FileName.bak'

This is the basic command to restore SQL database from backup. Fill in your own info and click Execute to perform the operation.

T-SQL Restore Full Backup

Note: If you receive “The tail of the log for the database has not been backed up” during restoration, you can either backup the log first if it contains work you do not want to lose:

BACKUP LOG DatabaseName TO DISK = 'FilePath\FileName.trn' WITH NORECOVERY

Or use WITH REPLACE or WITH STOPAT clause to perform restore and overwrite the contents of the log:


2. If you want to restore database from differential backup, then the commands will be:

RESTORE DATABASE DatabaseName FROM DISK = 'FilePath\FullBackupFileName.bak' WITH NORECOVERY
RESTORE DATABASE DatabaseName FROM DISK = 'FilePath\DiffBackup

Basically, add WITH NORECOVERY in statement is telling SQL Server to wait for more operations. You need to use WITH RECOVERY to finish the restore, or you may cause your SQL database stuck in restoring.

T-SQL Restore Differential Backup

3. If you’ve backed up multiple databases into multiple bak files, and want to restore them at once, you need to use a little more complex T-SQL script, please refer to restore multiple SQL databases.

Method 3. Restore SQL database from backup using command line

1. Search for “cmd” in Windows search bar and run Command Prompt as administrator.

2. Enter the following command to restore a full backup, press Enter to execute it.

SqlCmd -E -S ServerName -Q “RESTORE DATABASE DatabaseName FROM DISK='FilePath\FileName.bak'”

CMD Restore Full Backup

To restore database from differential backup, the commands would be:

SqlCmd -E -S ServerName -Q “RESTORE DATABASE DatabaseName FROM DISK='FilePath\FileName.bak' WITH NORECOVERY”
SqlCmd -E -S ServerName -Q “RESTORE DATABASE DatabaseName FROM DISK=' FilePath\FileName.bak' WITH RECOVERY”

CMD Restore Differential Backup

3. If you need to perform the same task frequently, you can also type the commands in a text editor and save it as a batch file with .bat extension. Thus you can double-click the batch file to execute the operation, or use Windows Task Scheduler to automate the task.

For more related info, please refer to batch script to backup SQL Server.

Method 4. Restore SQL database(s) from backup easily and centrally

The above methods can help you restore SQL database from backup, but they all have some inconvenience in one way or another. If you want to backup and restore SQL Server flexibly with the simplest operation, you can try the professional tool AOMEI Cyber Backup.

It can backup multiple databases at once via simple clicks, and manage the backup tasks centrally. This allows you to restore one or more databases from the created tasks, without the need to find specific bak files from local drive or network paths. Besides, it even enables you to backup and restore SQL database from one server to another.

Download the 30-day free trial to have a try:

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

🔹How to create SQL databases backup:

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 created task will be listed in the Backup Task tab. Then you can edit it or restore SQL database from backup anytime 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.


2. Select a backup version that you want to restore. If you’ve backed them up several times (full or differential), you can also make choice among the backups of different dates and modes.

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.


  • "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".



In this article, I introduced several different ways to restore SQL Server databases from backups, such as SSMS GUI, T-SQL, and AOMEI Cyber Backup, which combines the advantages of these methods.

With this tool, you can not only backup and restore SQL database, but also manage other types of computer data. For example, you can perform centralized system backup of all computers on network.

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.