Restore TDE Enabled Azure SQL DB to On-Premises Server


By:   |   Updated: 2020-09-21   |   Comments (2)   |   Related: More > Restore


Problem

For on-premises SQL Server databases that have TDE enabled, we need certificates to perform a restore to another SQL Server. Do we have the same requirements for Azure SQL databases that have TDE enabled? Is it possible to restore an Azure TDE enabled database on-premises? Can we use a similar approach of restoring a TDE enabled Azure SQL database to an on-premises server?

Solution

To learn about transparent data encryption (TDE), you can refer this tip for the details. You can refer to this link for additional tips related to SQL Server encryption.

In this earlier tip, we cover how to perform a restore of a database that was originally enabled for TDE, but later disabled. You can see the steps that need to be performed for performing a restore. Even after disabling TDE, restoring the database backup to another server is not straightforward.

In this tip, we will see how we can perform a database export to a BACPAC file and then complete an import on both an on-premises server and to an Azure SQL database.

Restoring a TDE enabled database to a different SQL Server

You can use the scripts from this tip to enable TDE on an on-premises database.

Once done, perform a backup of the database and try to restore to another on-premises server. You will get this error message as expected.

restore database command

Again, you will have to go through the steps outlined in this tip to perform a successful restore.

Create BACPAC of a TDE enabled on-premises database

You can export the same on-premises database that you created earlier to a BACPAC file and then import to another server and see if that works.

For exporting to BACPAC, follow the steps below.

Right click on the database in SSMS, go to Tasks and click on Export Data-tier Application...

ssms menu options

Once done, you will get an introduction page, click Next and you will see this screen where you can browse to a folder and provide a name for the BACPAC file.

export database

Once done, click Next and you will see this screen where you can select Finish to complete the process.

export database

This process may take some time based on the size of the database, but at the same time you saw how easily we can export the contents of the database using this method.

Once the export process completes, you will see this screen where you can view the confirmation.

export database

Import BACPAC file on to another on-premises server

Once the process is done and the BACPAC file is available, you can use the file for importing as a database.

You can connect to another instance and try the import process.

Right click on the Databases node and click on Import Data-tier Application as shown.

import database

Once done, you will get an Introduction page, click on Next and you will see this screen. Use the Browse option to select the BACPAC file you wish to import.

import database

We will be using the BACPAC which we exported in the previous section. Click Next and you will see this screen where you can view the option to provide the database file locations.

import database

Once done, click Next... In the next window, you can view the summary of the settings.

import database

Click on Finish to complete the import process. The process will complete in some time based on the size of the data getting imported. You will see this screen once the import completes.

import database

Checking the imported database

As you can see the imported process has completed successfully. You can go to the SQL instance to view the imported database.

Here, you can see the exported BACPAC of a TDE enabled database was imported fine to a different SQL Server instance.

sql database list

Also, when you run this query on the source SQL Server instance where this database resides, you will see the below output that shows it is encrypted.

select name, is_encrypted, * from sys.databases where name = 'onprem' – provide your DB name
query for encryption status

The output of the same query on the destination server is as below which shows it is not encrypted.

query for encryption status

From this demo, you saw how we could perform an import/export process to recreate a database on a separate server where the source database was using TDE.

If you want to encrypt the database on the destination, you would need to go through the steps outlined in this tip to complete the encryption process.

Import BACPAC from on-premises server to Azure server

Follow the steps as shown earlier in the tip for importing the on-premises BACPAC file to Azure.

During the import process on the Azure SQL Server, you will see this screen where you need to select the correct Azure database settings.

import database

Once done, follow the rest of the steps as shown earlier in the article to complete the import process and the database is now available for use.

You can use the below queries to check the imported database once the import is successful.

select name, is_encrypted, * from sys.databases where name = 'onprem'

select db_name(database_id), encryption_state from sys.dm_database_encryption_keys
query for encryption status

Above you can see that the imported database is TDE enabled by looking at the is_encrypted column. This is the default behavior in new Azure SQL database deployments, when a database is created it is encrypted. You can check the details of the system catalog sys.dm_database_encryption_keys in the Microsoft documentation.

Below are the various encryption states in the database based on the documentation which is shown in the second query results.

0 No database encryption key present, no encryption
1 Unencrypted
2 Encryption in progress
3 Encrypted
4 Key change in progress
5 Decryption in progress
6 Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

Check TDE feature on an Azure SQL Database

From the Azure portal, you can view the status of TDE on the database overview section.

azure sql db

This feature is on by default for all new Azure SQL database deployments.

You can run the below queries on the Azure SQL database to check the encryption status.

select name, is_encrypted, * from sys.databases where name = 'mo'

select db_name(database_id), encryption_state from sys.dm_database_encryption_keys

Below is the output of the queries.

query for encryption status

As you can see, the Azure SQL DB is encrypted. Even though this is an Azure SQL DB, for the second query, you are also able to see details of tempdb as this database gets encrypted when TDE is enabled.

Perform export of an Azure SQL DB

You can perform an export to BACPAC for the Azure SQL DB by connecting to it from SSMS. Just follow the steps shown in the earlier section for performing the export.

ssms export database

Continue to perform the next few steps as described earlier in the tip and complete the export to BACPAC. Once the BACPAC is ready, we will perform a restore to an on-premises instance as well as to Azure.

Import Azure SQL DB BACPAC to on-premises SQL Server

As shown earlier in the tip, follow the process for importing the BACPAC on an on-premises SQL Server. You will see the new database once the process completes.

database list

You can run the below commands on the on-premises database to validate the encryption details.

select name, is_encrypted, * from sys.databases where name = 'mo'

select db_name(database_id), encryption_state from sys.dm_database_encryption_keys

You can see that that there is no encryption on the imported database.

query for encryption status

Import Azure SQL DB BACPAC to Azure SQL DB

In this section, we will follow the same process for importing the BACPAC file from Azure on to the same Azure server but with a different database name.

We already exported the Azure SQL DB to a BACPAC, so we will use that file. Follow the steps for completing the import of the BACPAC.

Once the import process is complete, let's try the same queries.

select name, is_encrypted,* from sys.databases where name='mo'

select db_name(database_id),encryption_state from sys.dm_database_encryption_keys

Here you can see that encryption in enabled for the newly imported database. As seen earlier while importing the BACPAC file from on-premises to Azure, this is the default behavior for new Azure SQL database deployments.

query for encryption status

In this tip, you saw how we could use the export/import option for restoring a SQL database either on-premises or on Azure even though TDE was enabled on the database.

Next Steps
  • Try this demo on a trial version of Azure or use existing on-premises SQL Server installations
  • To learn more about Microsoft Azure, refer to this link on MSSQLTips
  • To learn more about encryption, refer to this link on MSSQLTips


Last Updated: 2020-09-21


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources





Comments For This Article




Tuesday, September 22, 2020 - 5:18:58 PM - Mohammed Moinudheen Back To Top (86518)
Thank you Srinath

Tuesday, September 22, 2020 - 3:41:35 PM - Srinath Back To Top (86516)
That's a great read. Thanks Moinu for yet another article. Please keep them coming.


download





Recommended Reading

Identify when a SQL Server database was restored, the source and backup date

How to migrate a SQL Server database to a lower version

SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Make Network Path Visible For SQL Server Backup and Restore in SSMS

SQL Server Database Stuck in Restoring State








get free sql tips
agree to terms