Monday, 27 August 2018

Converting a SQL Server .bacpac to a .dacpac

Microsoft SQL Server has two related portable file formats - the DACPAC and the BACPAC. Quoting Data-tier Applications:
A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.
A BACPAC is a related artifact that encapsulates the database schema as well as the data stored in the database.
When they say related, they're not kidding! Both of these files formats are based on the Open Packaging Conventions (a fancy way of saying it's a .zip file with some other bits), and cracking them open you discover that a bacpac file is basically a dacpac with a few extra files and a couple of different settings. Knowing this, it should be possible to manually convert a bacpac to a dacpac.

First, unzip the .bacpac file (using 7-zip, or rename to .zip and use Windows File Explorer’s Extract Archive).

Now do the following actions (you could do these programmatically if this is something you need to do repeatedly):
  1. Edit model.xml
    1. Change [email protected] to 2.4
  2. Edit Origin.xml
    1. Change ContainsExportedData to false
    2. Change ModelSchemaVersion to 2.4
    3. Remove ExportStatistics
    4. Recalculate the SHA256 checksum for model.xml and update the value stored in Checksums/[email protected]=’/model.xml’
  3. Remove directories _rels and Data
Now re-zip up the remaining files and change the file suffix back to .dacpac

To verify that the .dacpac is valid, try using SSMS with the Upgrade Data-tier Application wizard. Run it against any database and if you can proceed to without error to the "Review Upgrade Plan" step, you should be good to go.

No comments: