If you’ve read my blog post about encrypting your databases with sp_EncryptDatabase, then you’ll definitely want to learn more about my procedure I created called sp_BackupTDECert. Arguably speaking, backing up your Transparent Data Encryption certificates is just as important as creating the certificates in the first place. When you encrypt your data, the certificate used is needed if you have to restore that database to another server, or need to restore that certificate for some reason. Without a backup of your TDE certificate, you could find yourself in an unfortunate situation. This is where sp_BackupTDECert comes into play.

What is sp_BackupTDECert?

There is a good amount of syntax that is needed when backing up a TDE certificate. Sometimes it can be cumbersome and confusing. It can be even more overwhelming when you have to do that for a repeated number of certificates. Using sp_BackupTDECert enables you to just pass in a few parameters, and the rest is handled for you behind the scenes, abstracting some of those tedious lines of code.

How Does it Work?

When running sp_BackupTDECert, there are several parameters you can pass in depending on what you’re looking to do. Here’s an outline of the parameters below:

@CertificateName – The name of the certificate you would like to back up.

@BackupFolderPath – The folder path or UNC path you would like the certificate to be backed up to. Note that you do not need to pass in a file name or extension here. You only need to provide the path up to and including the folder name and the file name and extension will be generated for you.

@EncryptionPassword – This is needed for encrypting the certificate backup.

@Report – When set to 1, this will give you an inventory of TDE certificates on your server and their backup information.

NOTE: You should always run the first three parameters together, and just run the report parameter when you need an inventory of what you have.

See it in Action

So, when you create a certificate for TDE, after you attempt to encrypt a database with it, if the certificate and private key has not been backed up before, you’ll get a message that looks like this:

Let’s look at how we can resolve this by using sp_BackupTDECert. I can first run an inventory search to see what certificates I have on the server by running the following:

EXEC sp_BackupTDECert @Report = 1

When I run the above, I get the following result set:

This is pretty handy because I get the certificate(s) name, when the certificate becomes active, the expiry date, and the last time the private key has been backed up. As you can see here, the last column says NULL, meaning we have never backed up this certificate and private key before. We can then run the following in our example here to backup the SUPER_SECURE_CERTIFICATE to the location of our choosing:

EXEC sp_BackupTDECert @CertificateName = 'SUPER_SECURE_CERTIFICATE', @BackupFolderPath = 'D:\Backup\TDECert', @EncryptionPassword = 'C4N7H4CKM3'

Breaking down the above execution, I passed in the certificate name, backup folder path so it backs up the certificate to the TDECert folder located on the D:\ drive, and encryption password to encrypt the certificate backup. Here’s the results of our script:

As you can see, it has written an output of the certificate, and private key backup to the location we provided while also appending the date of the backup to the file name and has given us the correct file extensions. Now these cert and private key backups can be used in the event we need to restore an encrypted database to another server.

Now you Try

Here’s the code for sp_BackupTDECert so you can test it out in your environment. Please note to run this in a TEST environment first. I am not responsible for any accidents in your environment.
GitHub Link: https://github.com/TheUnfilteredDBA/DBA-Procedures/blob/main/sp_BackupTDECert.sql