We are just getting started with TDE. Eventually it will be in our Prod SQL Server environment and two major non-prod environments, but currently it is only in one non-prod system.
My question is about restoring these databases. We have several other environments (Enterprise and Standard Edition) and an offsite vendor that we sometimes send database backups to. For those, we will either have to use an intermediary server to turn TDE off before restoring, or back up with different keys.
But specifically, I am wondering how to restore from a TDE enabled prod environment to a TDE enabled non-prod environment. Once a TDE database is restored over without TDE being turned off, is it using the source server's keys going forward? I am wondering if turning TDE off and on (which would take hours) would be a requirement in this situation. Also note the strange behavior when I restored below. It appeared that it is carrying a historical key through a previous incarnation of the database, and when copying to a server with the same database name, is allowing the second server's key. It this correct?
I did the following in our non-prod environment consisting of two database instances on different VMs:
On instance 1:
- Enabled TDE on the database DBA,
- Backed up the cert, key, and database,
- Restored the backup of DBA to a new database DBA2,
- Backed up the database DBA2, On instance 2:
- Copied the DBA2 backup from instance 1,
- Restored DBA2.
Note that I did not create any certs or keys for DBA2, not did I copy any DBA certs and keys from instance 1 to 2.
Instance 2 has its own unrelated DBA database with TDE certs and keys. TDE is not enabled on DBA2 after restore.
So it seems like:
- the instance 1 DBA key got carried over into DBA2 in step 3, and
- the restore may have used the DBA key from instance 2 in step 6, even though instance 1's DBA and instance2's DBA are separate entities.
Here is the encryption status on these databases:
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length FROM sys.dm_database_encryption_keys where db_name(database_id) like 'DBA%'
dbname encryption_state percent_complete key_algorithm key_length DBA 3 0 AES 256 DBA2 3 0 AES 256
dbname encryption_state percent_complete key_algorithm key_length DBA 3 0 AES 256