Restoring multiple databases in a SQL Server instance

All we need is an easy explanation of the problem, so here it is.

Could you advise please, or point me in the direction of an appropriate article that could help.

I need to upgrade a SQL 2008 R2 instance to SQL 2019. I am proposing to backup the databases in the source instance and restore to a new more up-to-date host server. What is the best way to go about the restores of these many databases without doing them one at a time? Thank you

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You could use either of the following tools to accomplish restoring multiple databases at one time:

  1. dbatools – I’ve personally never used it but have heard many good things. I believe requires a bit of scripting.

  2. Microsoft’s Data Migration Assistant – Has a GUI and can proactively point out issues with migrating to the new server. But can be a little bit of a pain in some oddball scenarios. Overall a good tool, in my opinion though.

Or you could generate your own SQL restore scripts a number of different ways. For example, you could sys.databases and / or sys.database_files.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply