When working with Azure SQL databases one of the challenges is how difficult it is to move data across environments. Say for example we have a production database setup in Azure SQL and the Dev environment on premise. We need to extract a copy of the production database in order to troubleshoot the issues. The only way to do it is exporting the database from Azure portal. Microsoft Azure do not give us access to the backups so we end up having to use Data Tier Application (DAC) as the method for import export of data.
The exported data and schema are saved in a bacpac file which is available in a storage account.
Once we download the bacpac file we can import the same into the VM and often this would go through without a problem for small databases that don’t use any advanced features. Occasionally we might use a feature that is available only in Azure or the data is too large for importing into the VM. In such cases when we try to import the bacpac file we will get compatibility error messages which prevent the export from going through. There are two methods that can be used to address errors that arise out of such situations. They are variations of the same approach but one is less complex and that is what we will explore here.
The Bad way
Most often when you google how to modify a bacpac file you will see links with the bad approach mentioned. E.g.
https://techcommunity.microsoft.com/t5/azure-database-support-blog/editing-a-bacpac-file/ba-p/368931
in this method the steps are
- Rename the file to Zip
- Extract Model.xml
- Modify Model.xml – this file contains the scripts to create procedures and table etc – just make whatever changes you want like you would normally in SSMS.
- Calculate Checksum for Model.xml
- Extract Origin.xml — this file contains the checksum value for the Model.xml file so that SQL knows the files haven’t been tampered with.
- Update Checksum val in Origin.xml
- Add Origin.xml and Model.xml back into the Zip Archive
- Rename Zip to Bacpac
The problem with the above approach is having to extract and recompress the files which often result in error message saying bacpac file is corrupt.Often this is due to the file getting compressed while adding it back to the Archive and Incorrect MD5 etc
Instead, we can avoid all this hassle by simply using the below approach: –
The Good way
- Rename the file to Zip
- Extract Model.xml
- Modify the Model.xml– this file contains the scripts to create procedures and table etc – just make whatever changes you want like you would normally in SSMS.
- Rename the file .Bacpac
Notice in this case we simply extracted the Model file and then renamed. Zip back to bacpac file.
Now we can restore the bacpac file using command prompt, using the original bacpac file and the modified Model file.
Simply Open command prompt and navigate to bin folder for you SQL installation
In my case it is C:\Program Files\Microsoft SQL Server\160\DAC\bin
In here you will find the exe SQLPackage.exe , this is the program used to import or export data using Data tier application in SQL server.
It accepts some parameters which are explained below.
Action – In this example we want to import data.
SourceFile – In this example it’s the location of the bacpac file we just used.
TargetDatabaseName—The name of the database we want to import the data into. (Automatically created if not exits)
TargetServerName , TargetUser, TargetPassword – Login credentials to the VM , Azure SQL etc where we want to import the data
ModelFilePath—This is the most important switch in the command line, It tell the SQL package that we should bypass any Model.xml file found inside the zip file and use the one we modified earlier. This way we don’t have to recompress and add the model file back into the Zip file or sit and calculate the Checksum etc.
C:\Program Files\Microsoft SQL Server\160\DAC\bin>sqlpackage /Action:Import /SourceFile:”D:\temp\Demo.bacpac” /TargetDatabaseName:Demo1 /TargetServerName:localhost /TargetUser:sa /TargetPassword:MYSTRONGPASSWORD /ModelFilePath:”D:\temp\model.xml” /TargetTrustServerCertificate:True
After executing the above command, we will see the same type of output we would see normally in the GUI.
This is a much simpler and easier way to import data into SQL Server using Bacpac file. My biggest problems when using the old method was the time taken to compress and decompress large zip files. How frequently the files get corrupt and the need to use PowerShell etc.