Best way to copy DDL and Data from a SQL Managed Instance Database to a SQL 2019

Question

Best way to copy DDL and Data from a SQL Managed Instance Database to a SQL 2019

DanielP

SSCrazy

Points: 2302
More actions
May 5, 2021 at 10:21 pm

#3879850

Hello,
What is the best way to copy a database from a SQL Managed Instance to an on-premise SQL 2019 Instance. Back and restore doesn't work (supported).
I have tried Extract or Export trying to generate a .dacpac file or .bacpac file but no luck. Our core database has many calls to other databases on the Managed Instance and generates many errors trying to create the files.

External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element

Any 3rd party tools recommended. Want an easy way to deploy basically the DB Objects and data locally from a MI environment.

Thanks,

Daniel

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

Redgate's SQL Compare will get the schema back (Disclosure, I work for Redgate Software)

For data, we have data compare, but depending on how much data, this may or may not work. I think bcp is the best way to move lots of data. You can script a bcp out/in to files locally or blog storage, but are you trying to pull back all data? If this is a one time thing, that's easy. If you do this regularly for dev refresh, that's likely to be a hassle. I'd invest in a test data set instead

DanielP SSCrazy Points: 2302 More actions · Answer 2

Thanks Steve. This is Daniel P. I am using Visio Studio to generate the .DACPAC file because you can uncheck 'Verify extraction' so it doesn't error on other database dependencies. But when I try to Publish it to my (local) SQL Environment it fails when I 'Publish' because it tries to create the new database using the SQL Managed Instance data file path. I don't know how to modify it.

I can use the option to 'Generate Script' and first create the database locally and remove the Create Database and logins part and just have it create the schema. This works fine. But the Generate Script option doesn't include the data. As per some other articles if you change the .DACPAC file to a .zip file and open it, you will see that it creates BCP files. I guess I can automate brining in the data but was hoping to just be able to change the .DACPAC to just bring over the SCHEMA and DATA. I did look at the XML files in the .DACPAC but couldn't see how to comment that out.

I have used Redgate software previously so may look at that again. There should be an easier way to to take a 'Copy Only' backup in SQL MI and then use that backup to restore locally on a SQL 2019 instance.

Thanks,

Daniel

This reply was modified 2 years, 11 months ago by DanielP.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 3

This might help: https://stackoverflow.com/questions/30939180/change-logical-and-physical-file-name-when-deploying-dacpac

I'll post a note and see if anyone has an idea

DanielP SSCrazy Points: 2302 More actions · Answer 4

I was finally able to restore most of the databases from SQL Managed Instance to a Local SQL 2019 Instance.

I used Visio Studio 2019 to 'Extract Data-tier Application (.dacpac). I unchecked 'Verify extraction and others and choose to 'Extract schema and data'. I then changed the .dacpac to .zip and them copied out and modified the 'model.xml' file. I was then able to edit the XML to change the file locations to a local path(s). I also fixed some minor issues with no 'ADD groups' and Encryption settings from Managed Instance. Once modified you need to copy it back to the zip file and rename it back to .dacpac file.

Then download and run DacChkSum.exe from https://github.com/gertd/dac/tree/master/drop/debug to fix the CheckSum issue when the 'Model.xml' file is modified. Then need to replace the new CheckSum value in the Origin.xml file and put it back in the zip file. Once done, rename it back to .dacpac. I then was able to 'Publish Data-Tier Application. It then created the database, DDL and populated the data.

But on my Main DB which has a ton more DB Objects it fails because the Model.xml file doesn't have the proper order for the SQ View creates. It fails when the SQL View tries to be create but references a SQL View that doesn't exist yet. We have way to many SQL views to easily manipulate the Model.xml file.

Any suggestions? It shouldn't be this difficult to take a backup of a DB in SQL MI and restore on a non-MI environment.

Thanks,

Daniel

This reply was modified 2 years, 11 months ago by DanielP.

DanielP SSCrazy Points: 2302 More actions · Answer 5

DanielP

SSCrazy

Points: 2302

May 9, 2021 at 3:30 am

#3881042

Is there a way to continue on errors?

DanielP SSCrazy Points: 2302 More actions · Answer 6

I end up removing all the SQL Views before creating the Extract .dacpac. I found about 20 older DB Objects that wouldn't compile because of schema changes. I was able to clean them up from the mode.xml and finally got it to restore.