Check if my database instance on SQL server is encrypted by TDE?

I have a question about SQL server's transparent encryption (TDE). I need to dump a database instance, which will be restored by another DBA remotely by dumped data files. I was asked to make sure the dumped data files has no TDE so DBA can restore it. I checked online, and I found a query to list the encryption status as follows:

SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys; 

my database instance is not in the result at all. I run another query as follows:

SELECT db.name, db.is_encrypted, dm.encryption_state, dm.percent_complete, dm.key_algorithm, dm.key_length FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id; GO 

My database instance has value 0 for is_encrypted, and all other values null.

Does it mean my database instance is not encrypted at all?

1 Answer

If your output looks like this...

 name | is_encrypted | encryption_state | percent_complete | key_algorithm | ley_length -------------------------------------------------------------------------------------------- MyDatabase | 0 | NULL | NULL | NULL | NULL 

... your database, [MyDatabase], is NOT encrypted. Nor does it have a database encryption key configured.

If, however, any databases have non-NULLs in columns other than [is_encrypted] (e.g. [encryption_state] = 1), those databases are either encrypted, partially encrypted/decrypted or prepped for encryption.

Read up here for detail on encrpytion states: