By Delia / Last Updated August 20, 2021

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, how to restore them all at once?

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 alternative with GUI. Just pick the way you prefer.

Way 1. Restore multiple databases from bak files using xp_cmdshell

You could use a script to restore multiple databases in SQL Server. 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 use the following script to perform restoration (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 = ''' [email protected]+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 all 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

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 Centralized Backupper is a reliable centralized management solution to backup and restore SQL databases on all desktops, laptops, workstations and servers within LAN. It works with Windows 10/ 8.1/8/7/Vista/XP, Windows Server/2003/2008 (R2)/2012 (R2)/2016, Windows SBS 2003/2008/2011, Windows Home Server 2011 (32/64-bit) and supports SQL Server 2005 to 2019.

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

Download Free Trial Win PCs and servers
Secure Download

Install the software and its client packages properly, then you can gain controls over those client computers you want to manage (you can control your own machine, of course).

request control

How to backup multiple databases in SQL Server:

Step 1. Choose SQL Server Backup in Home tab to get started. Click + Add Computers to detect the computers with SQL database, and select one to backup in the pop-up window.

add computers

Step 2. Click + Add to detect all the instances on the selected computer. In the popping out window, you can select multiple databases or the entire instance(s).

select multiple databases

Step 3. Click on Step 3 and hit Add Storage to enter a share or NAS path as the storage end. Then you can also set up a Schedule to auto backup SQL database as you need, and click Start Backup to execute the task.

add share

◉ How to restore multiple databases from backup:

Step 1. Any created backup task will show in the Task tab. Click Advanced at the top-right corner and choose Restore.

restore databases

Step 2. Select the computer and all the databases you want to restore. In the upper right corner, you can also select the specific backup to restore (full or differential).

select databases

Step 3. Then, choose to Restore to original location or Restore to a new location. With the second option, you can restore databases to another SQL Server easily. Then, click Start Restore to execute it.

restore to original or new location

Overwrite the existing database (s): 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.

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

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.