How to Restore Multiple Databases in SQL Server (3 Methods)

How to restore multiple databases in SQL Server at once? Normally you can use efficient T-SQL script, or try a powerful software with fewer restrictions.

Delia

By Delia / Updated on June 13, 2023

Share this: instagram reddit

Can you restore multiple SQL databases at once

It is regrettable that SSMS GUI only supports restoring one database at a time. If you backed up multiple databases to multiple bak files, can you to restore them all at once?

The answer is YES. Generally speaking, you can use a valid T-SQL script to restore multiple databases in one go, but only if the backup files are named exactly after corresponding databases and have no additional info like date/time.

If you want to do it more flexibly, there's also an easy alternative with GUI. Just pick the way you prefer.

sql server

Way 1. Restore multiple databases from bak files using xp_cmdshell

You could use a script to restore multiple databases in SQL Server from a folder. But before that, please connect to the instance and enable xp_cmdshell.

1. Click New Query and enter the following command:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;

GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

Execute the query, and the feature will be enabled.

enable xp cmdshell

2. Then you can restore multiple databases of SQL Server using the script (please replace “D:\backup\” with your own folder that contains all the backup files):

DECLARE @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)

INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B D:\backup\*.bak'
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell

OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd NVARCHAR(MAX) = 'RESTORE DATABASE [' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '] FROM DISK = N''D:\backup\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.bak'' WITH FILE = 1, NOUNLOAD, STATS = 10'
EXEC(@cmd)

FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END

restore multiple databases

✎Note: If you receive an error “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”, the solution is just as the prompt says.

For example, you could modify the restore command to “…WITH FILE = 1, REPLACE, NOUNLOAD, STATS = 10”

Way 2. Generate script to restore all databases in SQL Server

Alternatively, you could use a script to generate restore commands for all the databases in SQL Server, and combine them as a new script to execute.

1. Click New Query and enter the following commands in SQLQuery window:

DECLARE @folderpath VARCHAR (1000)
SELECT @folderpath = 'D:\Backup\' -- Backup Location
SELECT 'RESTORE DATABASE['+NAME+'] FROM DISK = ''' +@folderpath+name+'.bak'' WITH NORECOVERY,
REPLACE, STATS = 5'
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')

This will generate a series of commands to restore multiple databases in SQL Server from bak files of the same names.

2. Right-click any command in Results, choose Select All and then Copy them (or use Ctrl + A and Ctrl + C).

generate commands

3. Paste these commands to the SQLQuery window as a new script to Execute. It will restore all databases of SQL Server from the bak files with corresponding file names.

restore all databases

Way 3. Backup and restore multiple SQL databases with GUI (easiest)

The above scripts are only applicable when your bak files named exactly according to the SQL databases, which is quite restrictive in practice. Therefore, I would like to provide a more convenient way to backup and restore multiple databases, or even the entire instance.

AOMEI Cyber Backup is a reliable centralized management solution to backup and restore SQL databases on all desktops, laptops and servers within LAN.

It works on Windows 11/10/ 8.1/8/7, Windows Server 2022/2019/2016/2012 (R2)/2008 (R2), and supports backing up SQL Server 2005 to 2022. Besides SQL database, it also supports virtual machine backup of Hyper-V and VMware.

You can download the 30-day free trial and get a up-to time-limited discount:

Download Free TrialMicrosoft SQL Server 2005-2022
Centralized and secure SQL backup
  • Run the downloaded .exe file to install AOMEI Cyber Backup, and launch its main interface. Click Source Device -> Microsoft SQL -> Add Microsoft SQL.

Microsoft SQL

  • Choose Download proxy program or Copy link to download the Agent program and install it on the device with SQL Server installed. Then, switch to Already installed proxy to select the device.
  • Next, click icon -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

Add Device

That's it, you can schedule data backup for the added SQL Server now.

How to backup multiple databases in SQL Server:

1. Click Backup Task -> Create New Task, and choose the Backup Type as Microsoft SQL Backup.

Backup Type

2. Click on Device Name to specify the SQL instance and databases for backup. You can select one or multiple databases as you need.

Select Database

3. Select a Target storage to save your SQL backups. You can speicfy a local path or network path.

Choose Target

4. Configure the Schedule to execute the SQL database backup daily, weekly or monthly, and choose the backup method as Full, Incremental or Differential Backup.

Schedule Backup

Click Start Backup to create and execute the task. Once it's finished, you will find it in the Backup Task tab.

✍More useful features:

  • Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
  • Email Notification enables you to receive email notifications when the task is abnormal or successful.

◉ How to restore multiple databases 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 SQL databases from.

Select Content

3. Next, select the target location that you want to restore to. There are 2 options:

  • Restore to original location (by default): in-place recovery that can revert SQL databases to an older verison.
  • Restore to new location: select another target to perform the out-of-space recovery. You can specify the name of the new database and modify the storage location.

Restore to Original 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

✍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

The SSMS restore GUI allows you to restore only one database at a time. If you want to restore multiple databases in SQL Server at once, the most common approach is using T-SQL script. However, it has strict restrictions on backup file names, and make operations like restoring databases to another instance hard to implement. Therefore, you could also try the SQL backup software - AOMEI Cyber Backup.

It makes the operation much easier. For example, you can auto backup SQL databases with several simple clicks, and restore multiple databases at once through equally easy operation.

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.