Azure

How to Import BACPAC File Created from Azure SQL Database?

How to Import BACPAC File Created from Azure SQL Database?

How to Import BACPAC File Created from Azure SQL Database?

When you need to export a database for archiving or for moving to another platform, you can export the database schema and data to a BACPAC file. A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation.

 

Import BACPAC File to On-Premise SQL Server :

  • C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin>
  • SqlPackage.exe /a:import /sf:\\Userdb0.bacpac /tsn:SERVER-SQL\DEV2016 /tdn:Azure_Test /p:CommandTimeout=2400

 

Error :

When you are try to import BACPAC File created from Azure Environment, you might encounter the following error if it consists of External Data Source Reference.

TITLE: Microsoft SQL Server Management Studio

 

 

Solution :

Drop external Tables and External Data Source in Azure SQL Database and create BACPAC File again without those references.

 

Drop External Tables and External Data Source

 

 

If you can’t recreate BACPAC without dropping the tables, you can follow these steps.

  1. Change the file extension to zip, then decompress it into a folder. Surprisingly, a bacpac is actually just a zip file, not something proprietary and hard to get into.
  2. Find the model.xml file and edit it to remove the section that looks like this:

 

 

If you have multiple external data sources of this type, you will probably need to repeat step 2 for each one.

 

Save and close model.xml.

 

Now you need to re-generate the checksum for model.xml so that the bacpac doesn’t think it was tampered with (since you just tampered with it). Create a PowerShell file named computeHash.ps1 and put this code into it.

 

Generate Checksum

 

Run the PowerShell script and give it the filepath to your unzipped and edited model.xml file. It will return a checksum value.

 

Copy the checksum value, then open up Origin.xml and replace the existing checksum, toward the bottom on the line that looks like this:

 

 

Save and close Origin.xml, then select all the files and put them into a new zip file and rename the extension to bacpac.

 

Now you can use this new bacpac to import the database without getting the error.