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
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.
You could use either of the following tools to accomplish restoring multiple databases at one time:
dbatools – I’ve personally never used it but have heard many good things. I believe requires a bit of scripting.
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