How do you copy a TDE-encrypted SQL Server database using T-SQL programatically?

Question

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.

• billinkc: Possibly of interest dba.stackexchange.com/a/30476/2131
• mygibbs: I'm actually using SSIS for the data copy from the source to destination dbs, but my main problem is migrating changes to the production db structure to the test/dev db e.g. a table is added to prod, I would have to change the SSIS package in order to reflect changes to test db (I'm using an Execute SQL Task to migrations to test db). Unfortunately I wasn't involved in the design for this application and if I was, I'd say migrate changes to prod/dev/test at the same time. But even then I'd have to maintain the SSIS package.
• billinkc: Right, but if you use some scripting magic, you don't care that things have changed. You push the button and all of your ETL is regenerated. It follows whatever pattern you establish, truncate and reload, incremental change, etc

Answer 1

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.

• mygibbs: Exactly, and that's the reason why we can't put the certificate on the destination server, because it's a lower environment from production and that will compromise security. And yes, there's PII in the production database, but I will be scrubbing that with UDF's when I move the data, I don't think the policy will be broken if I do that. Still, I'm hoping there's a way I can do this without having to maintain the package...