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

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.

Prerequisites of restoring multiple databases in SQL Server

Recovering multiple databases in SQL Server can be a complex process. To minimize downtime, there are several prerequisites that must be met before you begin:

• Have administrative privileges and access to the backup files.
• There must only be one database backup per backup file.
• There can only be two files in the backup file's database: Log file and Data file.
• Since we use the database name to name its physical files on the disk, the characters that can be used for the physical file names in the new database that we restore must be allowed.

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.

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

✎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).

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.

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.

• Run the downloaded .exe file to install AOMEI Cyber Backup, and launch its main interface. Click Source Device -> Microsoft SQL -> Add 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 -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

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.

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

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

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.

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.
• Archive helps you archive the backup files to an AWS S3 storage. 

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

2. Select a backup version that you want to restore SQL databases from.

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.

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 button to see restore details or cancel it.

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