By Delia / Last Updated December 20, 2021

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 centralized management 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

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:

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

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
GO
RESTORE DATABASE DatabaseName FROM DISK = 'FilePath\DiffBackup
FileName.bak' WITH RECOVERY
GO

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 Centralized Backupper Database.

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 Trial Win PCs and Servers
Secure Download

As a centralized backup tool, it can manage all the Windows desktops, laptops and servers within LAN. Install Centralized Backupper Database on the central machine and Agent package on client-side. Then you can Request Control for all the computers you want to manage.

request control

How to create a SQL Server backup task:

1. Navigate to Tasks > New Task > SQL Server Backup.

New SQL Server Backup Task

2. Click + Add Computers to detect the client computer with SQL instance in Step 1. Select one from the popping out window to proceed.

add computer

3. Then click + Add to specify the instance or databases you want to backup in Step 2.

Select Database

3. Move to Step 3, hit Add Storage to specify the target share or NAS. Then you can Start Backup.

Optionally, you can click Schedule to set up an automatic SQL Server backup.

Select Share or NAS

How to restore database from backup task:

1. The created backup task will show in the Tasks tab. Click Advanced on its top-right corner and choose Restore.

Restore Database from Backup Task

2. Select the computer and databases you want to restore from. 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 Database and Date

3. Then you will be given options to Restore to original location or Restore to new location (another server on another client computer). Then click Start Restore to execute it.

If you want to use the databases from the backup to overwrite the existing databases of the same names, please tick Overwrite the existing database (s), otherwise the recovery process will skip them.

Restore to Original or New Location

Conclusion

In this article, I introduced several different ways to restore SQL Server databases from backups, such as SSMS GUI, T-SQL, and AOMEI Centralized Backupper Database Edition, 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.