Technical Article

TDE Decryption Timer Script

,

At our company we Santize data from production to move to development environments. What I did was automated the entire process from backup, restore with cert\key to a "Sanitize" server, pull in a sanitization or masking script to mask the data. Remove the encryption to peform the final backup of the masked data to a Sanitized directory and move to the development server. The script can also be used to let you know when the encryption process has completed to move to a next step automating tasks. This turned out to be quite useful for the automation process I coded up. I hope you may find some use for the code at you shop.

Declare @dbid varchar(5),@encryptionstate int
Set @dbid = db_id('TDEDB')
loop:
SELECT @encryptionstate = encryption_state FROM sys.dm_database_encryption_keys
                                WHERE database_id = @dbid;
If @encryptionstate != 1
Begin
WAITFOR DELAY '00:02:00';
Print 'Waiting 2 minutes for Decryption to complete and checking status'
GOTO loop
End
Else
Begin
Print 'Beginning Final Sanitized Unencrypted Backup the Decryption Process has Completed.'
end

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(3)

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(3)

You rated this post out of 5. Change rating