Wednesday, May 8, 2019

Manually modifying a SQL Server *.bacpac file prior to importing

I encountered a situation where I needed to export a data-tier application (i.e. *.bacpac) from an Azure SQL Database and import it into a local instance of SQL Server. The *.bacpac created just fine; however, during import, it failed with a collation error. For various reasons, I did not want to modify the Azure SQL database and regenerate the *.bacpac. Instead, I found this helpful post:

http://inworksllc.com/editing-sql-database-azure-bacpac-files/

Those instructions utilize a file called dacchksum.exe that is available at:

https://github.com/gertd/dac/tree/master/drop/debug

Here is the process that I used:

  • Unzip the *.bacpac file (I use 7-zip, but any normal zip tool should work)
  • Open the model.xml file
  • Find the offending stored procedure, comment the body of the procedure, and add a return statement
  • Save and close model.xml
  • Zip all of the content into a new *.zip
  • Rename the new *.zip into a *.bacpac (e.g. newfile.bacpac)
  • Run the following command:
    dacchksum.exe /i:newfile.bacpac
  • Mark and copy the new checksum value
  • Open Origin.xml (from the unzipped folder)
  • Find the checksum (towards the bottom of the file) and replace it with the new checksum
  • Save and close Origin.xml
  • Zip all of the content into a new *.zip
  • Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
  • Import the new *.bacpac (using SQL Server Management Studio)
This process was successful ... except that importing a *.bacpac fails on the first error (it does not report all errors on the first run). After I fixed the first import error, it failed a second time on a different error, so I had to repeat the process multiple times. In my case, the *.bacpac was over 750 MB, so zipping everything multiple times was taking several seconds each time. I wondered if there was a more efficient process (where I did not need to zip, compute checksum, alter checksum, then zip again). I opened dacchksum.exe with ILSpy and found that the checksum is just a SHA256 checksum. Conveniently, PowerShell has a Get-FileHash cmdlet that does exactly that. Using Get-FileHash instead of dacchksum.exe, the revised steps are as follows:

  • Unzip the *.bacpac file
  • Open the model.xml file
  • Find the offending stored procedure, comment the body of the procedure, and add a return statement
  • Save and close model.xml
  • Run the following PowerShell command:
  • Get-FileHash fullpathto\model.xml | Format-List
  • Copy the new checksum value
  • Open Origin.xml
  • Find the checksum (towards the bottom of the file) and replace it with the new checksum
  • Save and close Origin.xml
  • Zip all of the content into a new *.zip
  • Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
  • Import the new *.bacpac (using SQL Server Management Studio)

This process was also successful, and was quicker because I only had to zip the content once.

No comments:

Post a Comment