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.

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 = ''' [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 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 Centralized Backupper Database 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. Besides SQL databases, it also supports files, partitions, disks and OS backup.

You can download the 30-day free trial and get a up-to 60% off discount:

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

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

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

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.

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

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

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.

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 Database Edition.

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.