unpac your bacpac and get your day bac

Recently i needed to do a high priority “clone” of a database hosted in Azure. My normal process for these in the past has been to use the Export Data Tier Application / Import Data Tier Application approach. A .bacpac file is generated, stored locally on disk or in cloud storage and then used to create a new database using the Import feature. I have used both the Azure management portal and SQL Server Management Studio. Seems the SQL Management Studio approach works across Azure subscriptions, or at least works easier for me since the Azure Portal approach fails with authentication issues when I attempt to import to a different subscription than the bacpac was captured from. Overall this approach works fine and has served me well. However. it can be a bit slow, especially when using the Management Studio. The Studio is running local on your machine, has to download the bacpac from the cloud and then push the all the resulting creates/inserts etc. up to the cloud DB.

Anyway, bac to the other day. The export took longer than usual, then the import just seemed to hang. After hours of watching it spin I started to get concerned and started looking a bit more closely. The tables it was hung on were log tables, that had not been cleaned out in a while. In fact the table it was “stuck” on had north of 17 million rows in it and after a few hours of processing had only completed 2-3 million. I had to use the snapshot I had taken, the data had moved on since and a go-back was not possible.

So, long story short(er): I started researching (googling) while the process continued. I found that the bacpac file was just a renamed .zip file and could be extracted and perhaps even edited. This gave me a way out. I extracted the bacpac file to see what I could find.

image

Here, nicely laid out is the package that is a bacpac file. Opening the Data\ folder will show you the data that is targeted for each table nicely arranged by table name.

image 

This made up set of tables shows the layout. Simply choose the tables that have expendable data, such as log entries, error logs, traces etc. Open those files and delete the .BCP files that are contained in them. In my case I had around 9 GB of un-compressed .BCP files that I could delete. 

Now just re-compress those files, rename the resulting .zip files to .bacpac and you are good to go. One note: when you re-zip those files, make sure to do so from the root of the bacpac folder that was extracted, not the parent folder you extracted to. If you compress the whole folder, the nesting of the files will be off and you will get an error on import like this:

bacpac-error

Which basically is telling you that you re-zipped at the wrong level. 

About me

.NET developer in upstate NY, USA
Current focus technologies: WPF, WCF
Intrigued by: Functional programming ala F#, Code Analysis, Math
Hobbies: this blog, go figure

Month List