I have three databases that I am migrating to Azure SQL Managed Instance. One is a Utility database, just a small collection of generic functions and procedures. One is the OLTP database for a web application. The third is a read-only archive of the OLTP database containing historic data for analysis and reporting as well as audit tables. I need to be able to restore these databases to on premise development and test servers. So far, I have been unsuccessful.
My first attempt was to use backup to Azure Blob Storage. But no matter what version of SQL Server I use locally, I always get an error that the Copy Only backup is from a newer version and restore is not supported.
Next, I tried Export Data tier application from SSMS v.18.1. Unfortunately, it never gets past the export. I get dozens of errors that read
External references are not supported when creating a package from this platform. One of the greatest strengths of Managed Instance over Azure SQL DB is the ability to do cross-database queries. I have a number of views and procedures that link tables in the two databases. Additionally, some procedures in the OLTP database call the functions in the Utility database. Since MI is not Azure SQL database, you would think this wouldn’t be a problem!
Searching Google, I found this question on Stack Overflow. So I tried the Visual Studio approach proposed as an answer. Although it appears to work, meaning it creates a multi-gigabyte file on my local machine, attempting to import that file using SSMS gives me an error that reads
No data is present in this file. So then I tried the second possible solution using
sqlpackage.exe. But that solution doesn’t work for me either as it only references exporting the schema. And if I try to export the whole database with
/Action:Export I get the same error message as SSMS gives.
I also examined this Database Administrators question. No help – that is what I originally attempted. If anyone has any other thoughts I would greatly appreciate hearing them!