First published on MSDN on Sep 25, 2016
Authored by Jeffrey Chilberto
Most of the time importing bacpac files from SQL Azure locally works without an issue but occassionally there will be an issue when a particular setting has been used that is not supported in the destination database version. There are many forum and stack overflow posts in regards to these sneaky little issues so here is a tip when working with bacpacs. They're zips...
Recently I had a requirement to bring a SQL Azure database down to my local SQL server and ran into a compatibility issue:
Error SQL72014: .Net SqlClient Data Provider: Msg 4631, Level 16, State 1, Line 1 The permission 'ALTER ANY DATABASE EVENT SESSION' is not supported in this version of SQL Server.
I was not able to alter the SQL Azure database in order to alter or remove any security settings so I was forced to take a different approach... in other words, I had to hack the bacpac.
- Make a copy of the bacpac...
- Rename the copy by changing the extension to .zip
- Open the zip and open the model.xml file
- In my situation I found the element containing the ALTER ANY DATABASE EVENT SESSION command and I deleted it.
- Rename the zip back to bacpac
- The bacpac does contain a checksum that it uses to validate the package. This needs to be updated. Fortuntately there is a git copy of the dacchksum.exe utility. Simply run against the new bacpac in order to get the checksum value.
- Rename the bacpac back to zip
- Inside the zip, open the origin.xml and update the checksum
- Rename the zip back to bacpac
- Try the import again!
Note: In four years I have only had to resort to this twice so I do view this as a last resort.
SQL Azure Backup References
Cheers!