SQL Server | 4 Methods to Restore Database from Backup

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.

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 Databases… in the menu.

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.

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

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:

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

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:

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:

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.

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.

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

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:

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.

◉ How to create a SQL Server backup task:

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

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

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

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

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

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.

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.

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.