We are trying to restore a large SQL Managed Instance database to On Premise running SQL Server 2016. We tried SQL Export Dump, since the database is large it is taking a long time to complete.
Are there alternative approaches to copy SQL Managed Instance DB to On premise Server running SQL Server 2016.
Good question. I am sure there is a better way of doing this, but the only way I have managed this so far is to
a) select the tables into one or more separate databases (so no indexes etc.)
b) Export data tier application(s) as a bacpac.
c) Import data tier application (it has to be Management Studio 18) into SQL 2016. If more than one database select * into again.
d) Add the users, then all the indexes and primary keys. We are fortunate as we have our database defined in a database project so we can just apply that.
You can try having a powerful VM in same region to speed up this process or as a alternate you can generate schema of your database and use BCP utility for big tables.
Use sqlpackage.exe in a Azure VM hosted in same region. I used this for a 10 GB database and it took 120 minutes to complete the task of export and then import.
