How to Restore Multiple Databases in SQL Server (3 Methods)
Can you restore multiple SQL databases at once
Unfortunately, the SQL Server Management Studio (SSMS) graphical user interface (GUI) has a limitation where it can only restore one database at a time. This means that if you have multiple databases to restore from separate backup files, you'll need to run the restore process individually for each database. This can be a bit more time-consuming and labor-intensive, especially if you have a large number of databases to restore.
The answer is YES. You can use a valid T-SQL script to restore multiple databases at once, but only if the backup files are named exactly after the corresponding databases and have no additional information like date/time in their names.
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
To restore multiple databases in SQL Server from a folder, first connect to the instance and enable xp_cmdshell, then use a script to restore the databases.
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. To restore multiple SQL Server databases, you can use a script that points to a folder containing all the backup files. Replace "D:\backup\" with your own folder path, and then run the script to restore all the databases.
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: The solution to the 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" is to simply follow the prompt's instructions. This involves backing up the log with the appropriate parameters or using the RESTORE statement with the specified clauses to overwrite the log's contents.
The restore command can be modified to include additional options, such as WITH FILE = 1, REPLACE, NOUNLOAD, and STATS = 10, to specify the file number, replace existing data, prevent loading of the database, and set the statistics timer, respectively.
Way 2. Generate script to restore all databases in SQL Server
You can use a script to generate restore commands for all databases in SQL Server, then combine them into a new script for execution. This approach can simplify the process of restoring multiple databases.
1. Click on the "New Query" button and enter the following commands in the SQLQuery window:
DECLARE @folderpath VARCHAR (1000)
SELECT @folderpath = 'D:\Backup\' -- Backup Location
The SQL command is to restore a database from a backup file, with the database name and backup file path specified in variables. The command is `RESTORE DATABASE [NAME] FROM DISK = 'C:\Backup\database_name.bak' WITH NORECOVERY`.
REPLACE, STATS = 5'
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
To restore multiple databases in SQL Server from bak files of the same names, you can generate a series of commands using the following script. This script will restore each database from its corresponding bak file, assuming they are all in the same directory.
2. Right-click any command in Results, choose Select All and then Copy them (or use Ctrl + A and Ctrl + C).
3. To restore all SQL Server databases from .bak files with corresponding file names, paste the following commands into the SQLQuery window as a new script and execute it. This will restore all databases.
Way 3. Backup and restore multiple SQL databases with GUI (easiest)
The provided scripts have limitations as they require exact file name matching with the SQL databases. To make backup and restoration more convenient, a more flexible approach is needed, allowing for backup and restoration of multiple databases or even the entire instance.
Qiling Backup is a centralized management solution that automatically backs up SQL databases on all desktops, laptops, and servers within a LAN, providing a reliable way to backup and restore SQL databases.
The software supports Windows operating systems from 7 to 11, as well as various Windows Server versions, and is compatible with SQL Server from 2005 to 2022. It also offers virtual machine backup capabilities for 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 This feature helps you automatically delete older backup versions, freeing up storage space.
- Email Notification You can set up email notifications to receive alerts when a task is completed normally or abnormally.
- Archive AWS Backup helps you archive the backup files to an AWS S3 storage.
✍Notes:
- The "Restore to original location" option should only be available if the original location still exists, otherwise, users should only be able to select "Restore to new location".
- If the original database has important data, it is recommended to choose "Restore to new location" to avoid overwriting or deleting it.
Conclusion
If you want to restore multiple databases in SQL Server at once, using the SSMS restore GUI is not an option. Instead, you can use a T-SQL script, but it has limitations and can make operations like restoring databases to another instance difficult. Alternatively, you can try using a SQL backup software like Qiling Backup.
With this tool, database management becomes incredibly easy. You can auto-backup SQL databases with just a few clicks, and even restore multiple databases at once with a similarly straightforward process. This makes managing your databases a breeze, saving you time and effort.
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.