I need to copy a TDE-encrypted SQL Server database to a separate, non-encrypted, SQL Server instance using T-SQL, but only by reading the schema of the source database. This is because I'm going to use this T-SQL in an Execute SQL Task on SSIS, and it needs to be able to account for changes to the production database without further maintenance to the SSIS package.
The database is fairly large and has all types of constraints (foreign, primary, default), indexes, views, user-defined functions, etc, so I will need to make sure I grab everything.
Unfortunately because it is TDE-encrypted, I cannot use backup/restore, attach/detach, or copy-move because it will throw a "security certificate not found" exception.
Is this possible using just T-SQL? Thank you in advance.
Best Answer
Using just T-SQL? Probably but not easily. A much better solution would be to simply put the certificate for the database on the destination server so that you can restore the database as needed to the destination server.
Given that your database has TDE enabled, I assume that there's PII in there that you have to keep encrypted. By moving it to another server without encryption you've probably just broken your data encryption policy.