I create a bacpac from azure and restore to my local on-prem server. However, it throws an error like “Please create a master key in the database or open the master key in the session before performing this operation”. The reason is one of the column in the table is encrypted. I don’t know how to restore the database from azure with master key and certificate etc. Do you know what I could do?

Thanks in advance,


Restoring a BACPAC isn't the same as restoring a SQL Server native backup (created using the BACKUP DATABASE syntax).

A native backup using BACKUP DATABASE is created by exporting all schema & data by backing up at the page level into backup files. By convention, these generally have a .bak file extension. A RESTORE DATABASE does the inverse to create a database by copying the data pages out of the .bak and into the database. (It's a little more involved than this, but that's the big picture.) If the source database is encrypted with TDE, the data pages in the backup are also encrypted.

A BACPAC is created by generating CREATE statements for all of the schema, then doing an export of data. The .bacpac file itself is actually just a zip file of the CREATE scripts & data export. When doing a "restore" of the BACPAC, it simply executes the CREATE scripts, then does an import of the data. If the source database is encrypted with TDE, the exported data within the .bacpac file is not encrypted.

If you want to create a TDE-encrypted database using a BACPAC, simply pre-create an empty database and enable TDE on that database. Then, import the BACPAC into that empty database, and Bob's your uncle.

  • In this answer and your comment you state "Then, import the BACPAC into that empty database". According to all documentation I can find this - importing bacpac into existing database - is not something that is possible. Can you elaborate please? – Justin Wignall Nov 23 '19 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.