How to Restore Multiple Databases at once in MS SQL Server

In the previous article, I show you How to Backup All Databases at Once in MS SQL Server. And in this one, I'm going to show you how to restore multiple databases at once using a SQL Query. as the MS SQL Server Management Studio does not give any option to do that. We need to go through these steps to achieve the goal.

Let's Begin.

As the first step, I will show you the query that you need to use for this task and after that, I will explain the places that you need to change in order to run this script in your SSMS

So as you can see in the above code and you need to do some changes there to get things done from this query.

As the first change, Let's see the first few rows and what it does.

As you can see in the above image I have created a table and I'm storing all the database names in that table. These are the backup file names that we get after we backup the databases. And keep in mind to add the DB names that are in the same folder. because we are using a one file path to restore. So you need to add those names manually. you can add as much as you need to add. remember to add the ID correctly.

The next thing is to add the DB backup folder path correctly to the path variable section. This needs to be changed in the following line as you can see in the below image.

And the last thing that you need to change is the following lines that we setting the path to the SQL Server internal folders. This is actually not a change just verify that this folder path is the correct one according to your SQL Server version. and if it is different please add it here as well.

In the Restore Command section, you need to add some string value to 2 places

In the first Move part, you can see the ‘LogicalName’, and in the second Move line you can see the ‘LogicalLogsName’. So these 2 need to change to your logical names.

So, how can we find the logical names?

you just need to commend this Restore Database command section that I show in the above image. and add this part to the above of that commented restore database section and run the whole script.

RESTORE FILELISTONLY FROM DISK = @PATH

and if you run the whole script you will output and that will show multiple tables and each table you will the Logical names and the Logical Log name just copy those 2 from one of the tables and replace the string.

and uncomment the RESTORE DATABASE section and comment or remove the above newly added code section and run the script and you can restore all the databases that you want to.

After changing these things you just need to run the script and you can see the databases are restored to your server in a while. if you have lots of DBs it will get some time to restore.

Click the link if you want extra knowledge about How to get all file names that are in a folder to a text file.