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 = ''' +@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.
Qiling BackupSQL databases on all desktops, laptops and servers within LAN. Qiling Backup is a reliable centralized management solution to backup and restore
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:
✍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.
✍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 - Qiling 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.
Related Articles
- Microsoft SQL | How to Back Up All Databases of an Instance?
It's not difficult to back up one or two databases in MSSQL, but how to back up all databases of an instance at once? Normally you can try following 4 methods to do it. I'll also provide an easier alternative. - How to Restore SQL Database from Bak File? (3 Methods)
After completing the backup successfully, you can restore SQL database from bak file any time you need. In this post, you will find 3 methods to do this, including SSMS, T-SQL query and command line. - How to Use Batch Script to Backup SQL Server Databases?
Compared to other methods, a batch script is especially useful when you want to back up batches of databases regularly. This post shares batch scripts for backing up SQL Server databases, and an alternative with equal functionality. - Guide | Backup and Restore SQL Database with Command Line
You can backup and restore SQL database via command line, which is especially useful in Express editions that lack schedule backup capabilities. Here's a quick guide for you to backup database using SQLCMD.