Editing SQL Database (Azure) bacpac files
A series of unfortunate events led me to the necessity of restoring a table from a database backup. We configured our SQL Database in Windows Azure to be exported daily and, after retrieving the latest bacpac file, I attempted to import it into a local server to get at the missing data.
Everything was going great until the import wizard blew up on an error:
“Microsoft Restore Service returned an error: Error encountered during the service operation. Could not import package. Error SQL72014: .Net SqlClient Data Provider: Msg 313, Level 16, State 2, Procedure MySproc, Line 5 An insufficient number of arguments were supplied for the procedure or function dbo.MySproc1. Error SQL72045: Script execution error. The executed script: CREATE VIEW [dbo].[MyView] AS SELECT…”
Turns out the database, at the time of export, had a view that used a stored procedure which changed since the view was originally created and the view was never updated to reflect the change. NBD, right? Not so much… Looks like the Microsoft Restore Service cares about that sort of thing, and, rather than failing on the object with the error, fails the entire restore process. Not ideal– especially if what you really want is just some other table that has nothing to do with either the view in question or the stored procedure.
The only way to get around this is to edit the contents of the bacpac file. Thankfully, it’s just a ZIP archive, so rename the extension from bacpac to zip, and you can open and browse the contents. What you’ll find inside are a few XML files describing the database, and, if you open up the Data directory, you’ll find a directory per table, inside which there’s a BCP file containing the table’s data (I haven’t found an editor for the BCP files – know of one?).
Figure 1 *.bacpac root directory
The file that controls the structure of the recreated database is called model.xml and it’s located in the root of the bacpac file – this is the file you need to modify if you want to change the structure of the imported database.
Figure 2 model.xml snippet
You can modify the model.xml file to suit your need, although I don’t know if some changes may break the import process in other ways. In my case, I needed to remove the offending view, which I did.
After modifying the model.xml file, we also need to update the Origin.xml file, as that contains the checksum of the model.xml file, and since I’ve just modified model.xml, a new checksum needs to be generated.
Figure 3 model.xml checksum in Origin.xml
An earlier search of the interwebs lead me to the checksum generator for bacpac files – luckily for me. To use dacchksum.exe, I had to follow these steps:
- Update the zip file with the modified model.xml
- Rename the zip file with the bacpac extension
- Run dacchksum.exe /i:database.bacpac (where database.bacpac is the name the bacpac file)
- Update Origin.xml in the bacpac file with the new value provided by dacchksum.exe
Dacchksum.exe provides the following output:
Figure 4 dacchksum.exe output
Once I had the updated bacpac file without the problematic view, I was able to import the database successfully and proceed with restoration.