CODEWITHSUNDEEP
sql-server-2008encryptiontde
Neil Weicher
Neil Weicher

Reputation: 2502

SQL Server TDE - how to tell when complete?

Is there a way to tell when a database is completely encrypted when using SQL 2008 TDE? I.e.,

ALTER DATABASE mydb SET ENCRYPTION ON

The only way I can think of is to keep trying to detach mydb. It won't detach until all pages are encrypted. However, I'd like to have a cleaner way that does not involve detaching?

Thanks.

Upvotes: 6

Views: 7574

Answers (2)

Vlad Kirov
Vlad Kirov

Reputation: 39

old, but I'll just place it over here. pay attention on "percent_complete"

SELECT DB_NAME(database_id) DbName,
  encryption_state EncryptState,
  percent_complete,
  key_algorithm KeyAlgorithm,
  key_length KeyLength,
  encryptor_type EncryptType
FROM sys.dm_database_encryption_keys;

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453278

Looks like this information can be found in sys.dm_database_encryption_keys.

encryption_state = 3 indicates the database and logs are encrypted. encryption_state = 2 means Encryption in progress in which case you might want to also look at the percent_complete column.

Upvotes: 4

Related Questions