Sql-server – Restoring MS SQL TDE database question

sql servertransparent-data-encryption

I'm new to SQL Server TDE and have a question.

I have two VM's (VM1 = Source and VM2 = Target). On the source machine, I executed all the required steps to enable my Test database as well as backing up the certificate and private key. No issues here.

On the target machine, i executed the following t-sql commands 

CREATE MASTER KEY ENCRYPTION

CREATE CERTIFICATE
FROM FILE
WITH PRIVATE KEY(
FILE
DECRYPTION BY PASSWORD
);

without any issue and successfully restored my Test TDE database.

On the source machine, I had to run this command

CREATE DATABASE ENCRYPTION KEY

but I didn't have to when I did the restore on the target machine. Here's my question: Is my Test database on the target machine TDE enabled?

Best Answer

When you say:

On the source machine, I executed all the required steps to enable my Test database as well as backing up the certificate and private key. No issues here.

We don't really know the steps you took.

Here is a query you can run to see if the database is encrypted on the target server. Look at the EncryptionState column. 

SELECT 
    db_name(database_id) as DatabaseName, 
    Case 
        when encryption_state=0 then 'No Database Encryption'
        when encryption_state=1 then 'Unencrypted'
        when encryption_state=2 then 'Encryption In Progress'
        when encryption_state=3 then 'Encrypted'
        when encryption_state=4 then 'Key Change In Progress'
        when encryption_state=5 then 'Decryption In Progress'
        when encryption_state=6 then 'Protection Changes In Progress'
    end as EncryptionState,
    percent_complete
FROM sys.dm_database_encryption_keys
Related Question