Export and import databases using Always Encrypted

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This article describes how to export and import databases containing columns protected with Always Encrypted.

When you export a database, all data stored in encrypted columns is retrieved from the database in the encrypted form (ciphertext) and put into the resulting BACPAC. The resulting BACPAC also contains the metadata for Always Encrypted keys.

When you import the BACPAC into a database, the encrypted data from the BACPAC is loaded into the database and Always Encrypted key metadata is re-created.

If you have an application that is configured to query encrypted columns stored in the source database (the one you exported), you don't need to do anything special to enable the application to query the encrypted data in the target database, as the keys in both databases are the same.

For detailed information about how to export and import a database, see:

• Export a Data-tier Application
• Import a BACPAC File to Create a New User Database
• Export an Azure SQL database to a BACPAC file
• Import a BACPAC file to a database in Azure SQL Database
• SqlPackage

Permissions for migrating databases with encrypted columns

You need ALTER ANY COLUMN MASTER KEY and ALTER ANY COLUMN ENCRYPTION KEY on the source database. You need ALTER ANY COLUMN MASTER KEY, ALTER ANY COLUMN ENCRYPTION KEY, VIEW ANY COLUMN MASTER KEY DEFINITION, and VIEW ANY COLUMN ENCRYPTION DEFINITION on the target database.

You do not need to have access to column master keys configured for the encrypted columns, as the data stays encrypted during the export and import operations.

Next steps

• Develop applications using Always Encrypted

See also

• Always Encrypted
• Back up and restore databases using Always Encrypted
• Migrate data to or from columns using Always Encrypted with SQL Server Import and Export Wizard
• Bulk load encrypted data to columns using Always Encrypted