When working with Azure SQL databases one of the biggest irritants is the lack of access to backup files. A lot of the time we need to refresh test environments with copies from productions and the only real option is to use the DAC ( Data Tier Application) to export schema and data from the Azure SQL database platform. This in itself wouldn’t be a problem expect that certain database objects can be found only in Azure SQL PaaS and not in on prem installations or vice versa.
In this case I have a bacpac file where I want to make two changes, the first is to remove login from within the Schema Metadata since it has dependent objects and the other is to prevent loading a large table which contains log info and doesn’t serve any purpose and only delays the loading of the data into the database. The basic thing to keep in mind about Bacpac files are :-
- They are basically a zip file
- There is a file called Origin.xml that contains the checksum to confirm the file isn’t corrupt, if the contents are modified the checksum needs to be updated
- The Schema information is saved in a file called model.xml, this is where you can alter procedures, drop logins etc.
- The folder Data contains BCP files that are stored in folder, one folder per table.
Step 1
Rename the backpac file from say example.bacpac to example.zip
Step 2
Do not extract the file contents, simply open the zip file using WinRar.
Step 3
Extract the file model.xml to your local harddisk
Step 4
Open the file using a file editor like visual Studio or notepad++ etc
Step 5
Edit the file as needed.
Step 6
Add the modified file back into the zip file archive, this can be done by simply by opening the zip file in Winrar and right clicking the background to add a file into archive.
Step 7
If you need to delete data from a table you can do so by navigating to the data folder and identifying the folder with the appropriate table name. Open the folder and you will bac files. Do not delete the folder just the contents of the folder.
Step 8
Now that we have made the changes, we want we need to find the new checksum value for the model.xml file
Open PowerShell using admin rights and run the command Get-FileHash and the path to model file.
Copy the Hash value generated as the output.
Step 9
Extract the Origin.xml file from the archive to the local harddisk and copy past the Hash value into the origin.xml file
To the below for example
Step 10
Save and add the Origin.xml file back into the archive as we did in Step 6
Step 11
Rename the file back to example.bacpac and you are good to go.
Do not extract and recompress the files, this corrupts the file you can do all the operations directly within WinRar Utility itself.