Restoring from BACPAC times out in SSMS
When moving databases from the cloud to on-premise (usually for some advanced 'hacking', performance-tuning or just experimenting), I often use the BACPAC format. It is basically a zip file that encapsulates the schema and the contents of a database. I can export it with SSMS, and then re-import it the same way (I'm talking about Azure SQL and SQL Server, of course).
But now I had a BACPAC that I couldn't restore, SSMS failed with a timeout. The indices are not part of the BACPAC file (remember, it is a data-tier application), but the index definitions are included. And it turns out that recreating some of the indices took too long.
So what can I do? I can create the indices later, manually. But first, I have to somehow pull them out of the BACPAC file.
First, rename the bacpac extension to zip (again, it is just a zip file). Find the model.xml file in the zip. This contains all the schema information. You can just go ahead and delete the index definitions from this file, and then rezip the whole thing.
Only problem is that there is a checksum in the bacpac file and if you modify the xml and repack it, the importing fails, because the checksum is now different.
So the checksum must be updated as well. Find the Origin.xml file, which contains the checksums for the files in the BACPAC. You have to update the checksum for the /model.xml file, but first, you have to calculate the correct checksum.
There are some tools floating around the internet to do this, but if you have a PS terminal at hand, you can do it yourself with a simple line:
Get-FileHash '.\model.xml' -Algorithm SHA256
This gives you the checksum value that you have to put in the Origin.xml. And you're done.