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.
TDE with Automatic Database Seeding
Transparent Data Encryption on an Availability Group and Automatic Database Seeding
2018-03-08
2,600 reads