Restoring multiple databases in a SQL Server instance



  • 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



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

    1. https://dbatools.io/ - I've personally never used it but have heard many good things. I believe requires a bit of scripting.

    2. https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15 - 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 https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15 and / or https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-ver15 .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2