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

4.33 (3)

Share

Share

Rate

4.33 (3)