Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restoring a TDE enabled database question Expand / Collapse
Author
Message
Posted Saturday, March 22, 2014 4:25 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:49 PM
Points: 728, Visits: 5,226
So, I may need to enable TDE on some DBs in the near future, and I want to get everything clear in my head. I understand the basics of enabling TDE, and I understand that losing the certificate (the DMK) *WILL* result in a non-recoverable database, period. But, I have some questions on *restoring* a TDE enabled DB.

I'm presuming, if I need to restore a copy from our production environment to our QA, I would have to import the certificate used into the QA server. Once that's done, I expect (and plan to test on my home system) that I would then be able to simply restore my backup to QA.

But. Of course there's a but. Our normal backups are taken using a 3rd party application, Commvault. I've already asked the CV admin to look into what's needed and even if CV can backup / restore TDE databases. So, question is, does anyone out there have experience with *any* 3rd party backup solution and TDE database backup and recovery? Is it possible? Is it similar (when restoring) to the process for "manual" backups?

Thanks,
Jason
Post #1553780
Posted Saturday, March 22, 2014 7:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:29 PM
Points: 395, Visits: 1,992
I am not sure about commvault backups. But most of the third party tools have their own encryption methods. Please check the commvault software documentation.
Post #1553784
Posted Saturday, March 22, 2014 9:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
Just a guess, but I would think as long as you have the certificate used to encrypt the database with TDE it should not matter what backup software you use, native or 3rd party.

I would test it out on a small test database just for that purpose.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1553787
Posted Saturday, March 22, 2014 11:19 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:49 PM
Points: 728, Visits: 5,226
Lynn Pettis (3/22/2014)
Just a guess, but I would think as long as you have the certificate used to encrypt the database with TDE it should not matter what backup software you use, native or 3rd party.

I would test it out on a small test database just for that purpose.


Absolutely!

One advantage to having a proper QA environment. I could enable TDE on the QA version of one of the DBs (preferably taking a backup BEFORE enabling TDE to have a "clean" DB to return to if it doesn't work), have the end-user verify everything is working OK in the app, then give a try backing up / restoring from CV.

Thanks!
Post #1553794
Posted Monday, March 24, 2014 4:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 6,416, Visits: 13,798
jasona.work (3/22/2014)
So, I may need to enable TDE on some DBs in the near future, and I want to get everything clear in my head. I understand the basics of enabling TDE, and I understand that losing the certificate (the DMK) *WILL* result in a non-recoverable database, period. But, I have some questions on *restoring* a TDE enabled DB.

I'm presuming, if I need to restore a copy from our production environment to our QA, I would have to import the certificate used into the QA server. Once that's done, I expect (and plan to test on my home system) that I would then be able to simply restore my backup to QA.

But. Of course there's a but. Our normal backups are taken using a 3rd party application, Commvault. I've already asked the CV admin to look into what's needed and even if CV can backup / restore TDE databases. So, question is, does anyone out there have experience with *any* 3rd party backup solution and TDE database backup and recovery? Is it possible? Is it similar (when restoring) to the process for "manual" backups?

Thanks,
Jason

To restore a TDE enabled database to a new instance of SQL server you need the database backup and a backup of the certificate from the source server that is used to protect the database encryption key.

It's all in my guide at this link


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1553952
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse