Restore Azure SQL Managed Instance to onprem SQL Server

Question

Hi All,

We have a requirement where we need to restore back-up of Azure SQL Managed Instance to on-prem. Below are what we are looking for?

1) Can the back-up of Azure SQL Managed Instance database re-stored to any previous version SQL Server on-prem than to on what Managed Instance is currently running on? e.g., Azure SQL MI running on 2022 and on-prem SQL Server version is 2016.

2) If answer to above question is 'Yes', -

• What fallback versions are supported?

3) What are the pros and cons for such approach?

Appreciate your help and response,

Answer 1

Azure SQL MI running on 2022 and on-prem SQL Server version is 2016.

That's not possible, you can not restore a backup of a newer SQL Server version (here 2022) to an older one (here 2016); no way.
You have to migrate the database in an other way = mostly manually.

Answer 2

'BCP in/out' methods for restoring database locally will work completely or there are some reservations

BCP out is an export of the data as plain text or compressed native BCP format; BCP in to import.
A way to go, but you have to respect the right order to meet primary/foreign key constraint => as I wrote, manually work.

Answer 3

Using SqlPackage does work. Be aware, it is an extremely slow process, and it can be particular about database content.