3

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!

  • Can you post up the exact commands you used for SqlPackage.exe for the export and import of the database? – HandyD Jul 31 at 2:05
  • We just moved to SQL (MI) as well, and not being able to go back to on-prem was one of the cons that I mentioned during project planning. Have you looked into transactional replication? Otherwise, I think BCP might be your best bet. – Jonathan Fite Jul 31 at 2:23
  • You cannot directly restore backup from Azure managed instance to on premise that is not allowed( still) you have to rely on bacpac. if you could give more details I would try to reproduce your issue and get back – Shanky Jul 31 at 6:52
  • @Shanky Create two databases Online and Archive. Create two identical tables, Orders in Online db and OrdersArchive in Archive db. Create a View in Online vOrders that is a UNION of the two tables, referenced like [Online].[dbo].[Orders] and [Archive].[dbo].[OrdersArchive]. Now try to create a bacpac of database Online. – CB_Ron Jul 31 at 18:06
  • @HandyD here it is. "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\sqlpackage.exe" /Action:Export /SourceConnectionString:"server=tcp:xxxxxxx.xxxxxxx.database.windows.net,1433;uid=xxxxxx;pwd=xxxxxxxxxxxxxxxxxxxxx;database=xxxxxxxxxxxxx" /OverwriteFiles:True /TargetFile:"E:\Temp\AzureDB\xxxxxxxxxxxxxxxxxxx.bacpac" – CB_Ron Jul 31 at 18:14
1

Bacup-restore from a MI is not supported. The backpac extraction fails when it finds few dependencies or unsupported features.This is because SSMS as it verifies your extraction. Try using SSDT (Visual STudio) and you can skip verify extraction (this is by default unchecked) :

  1. Open SQL Server Object Explorer

enter image description here

  1. Connect to your MI. Right click the database and click : Extract Data tier application.

Select Extract Schema and data.

This will succeed.

enter image description here

enter image description here

  1. Use the dacpac file created above to Deploy the database. Right click databases --> Deploy data tier application.

Data and schema :

enter image description here

  • I tried this approach. It created a bacpac file but when I try to import it using SSMS I get a message that it contains no data. Even though it is 3.5GB in size. – CB_Ron Jul 31 at 18:11
  • It will be a dacpac file if you select : Extract Data tier application. – Ramakant Dadhichi Aug 1 at 7:46
  • Sorry, you are correct, it was a dacpac. – CB_Ron Aug 1 at 16:15
  • 1
    I marked this solution as the answer because it is in fact working, or will when I clean up old views in the Online db that don't relate to current table columns. It has already successfully restored the Archive db. The part I was missing was I tried Import Data Tier Application, but it has to be Deploy Data Tier Applicationto get the dacpac to work. – CB_Ron Aug 6 at 17:03
  • 1
    Happy to have been helpful.cheers – Ramakant Dadhichi Aug 6 at 22:39
2

I've been able to export data from an Azure Managed Instance (MI) to a SQL Server 2012 system successfully without the need of Visual Studio.

  1. Using SQL Server Management Studio (SSMS) right click on the database in you want to copy choose Tasks > Export Data-tier Application. Choose where to save the .bacpac file that is created.

    • A .bacpac file is a zip file containing XML that defines your tables and relationships along with BCP files that store the data of each table.
  2. If you have 7zip extract the bacpac file to see the contents. If you don't, you can rename the file extensions from .bacpac to .zip and extract it using File Explorer (right click > Extract All...)

  3. Open the model.xml file in your favourite text editor and look for the attribute called CompatibilityMode. This will have been set to the compatibility level of the MI (currently 150). Change it to the compatibility level you are importing to. I was importing to SQL 2012 so changed it to 110.

  4. Remove any other incompatible features by commenting out the relevant line in model.xml.

    • For example, SQL 2012 can't do In-Memory tables so I had to comment out this section from the model.xml file:
    <!--<Element Type="SqlFilegroup" Name="[XTP]">
            <Property Name="ContainsMemoryOptimizedData" Value="True" />
    </Element>-->
  • Setting the Value to false may also work.

    1. Putting it back together. Microsoft have added some simple validation to tell if the bacpac file is corrupt or has been tampered with. If you have made any changes to model.xml file you will need to calculate a new checksum value for it. The value is stored in the Origin.xml file in the bacpac.
  • You can generate a new checksum value by using PowerShell:
Get-FileHash -Path C:\Path\To\Extracted\bacpac\model.xml | Format-Table -Property Hash

You should get an output like this:

Hash
----
ED8754D205946850F3CED0B033342BF3D88A43E146E57D861191AACC1AC1659A

Open the Origin.xml file from the extracted bacpac files and look for a line like this at the end and replace the existing hash value with the new one:

<Checksums>
  <Checksum Uri="/model.xml">ED8754D205946850F3CED0B033342BF3D88A43E146E57D861191AACC1AC1659A</Checksum>
</Checksums>

Make sure you don't add any spaces or extraneous characters.

Now zip the files back up making sure the top level of the archive is the files and not the folder you extracted everything too. If you open the new file you should see the xml files and not a single folder name.

You should now be able to import the bacpac into your non-Azure SQL database.

Some notes about bacpac backups:

  • They may not be transactionally consistent. For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database.

  • If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. To archive a larger BACPAC file, export to local storage.

  • I have tried what you suggest. But I cannot create the bacpac file from SSMS. I get this message: External references are not supported when creating a package from this platform. The reason is because I have two databases, one the OLTP database, and one that is a read-only archive. In the OLTP database, there are views that reference the archived versions of tables in the OLTP database. So that a user can lookup historical data, but not change anything that has been archived. The views are the UNION of the two tables with fully qualified names. – CB_Ron Jul 31 at 19:56
  • SSMS acts as a wrapper for the SQLPackage.exe in C:\Program Files (x86)\Microsoft SQL Server\<SQL_VERSION>\DAC\bin and has the Verify switch enabled by default. This answer on this question shows how you can use the SQLPackage from the command line to turn off validation. – KevH Aug 1 at 7:44
  • in my original post I detailed (with the same link you provided) how I had read that question and answer and tried it. It didn't work because the Verify option is not valid for /Action:Export, which is the only way to get the data also. – CB_Ron Aug 1 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.