Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server

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.

  5. 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.