Restoring multiple databases in a SQL Server instance

by

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment