Troubleshooting SQL Server 2014 Encrypted Backups May 7th, 2014

Vinod Kumar

My adventures of working encrypted backups still are on. There are couple of posts already on how to take encrypted backups – I highly recommend you to read them before getting into this.

When I wrote the first post, a lot of folks asked me how to know we have taken an encrypted backup and what are the things we need to keep in mind when working with encrypted backups. This blog will walk you through some of the basic troubleshooting techniques in first understanding what it entails to work with encrypted backups.

Did you backup your certificate

In our first post around SQL Server 2014: Encrypted Backups, we show it is important to take a backup of your certificates. Many a times some of these best practices can be overlooked. But if you take a backup using a certificate which was not backed up, we will be presented with the below warning every single time.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

I think the warning is quite self explanatory. This message will keep on coming till we take a backup. Just thought this is worth a mention and note here.

How to know which backups are encrypted

A number of times DBAs will want to know which are the backups and the corresponding certificates used to encrypt a given backup. The below query uses the DMVs to give you this details.

SELECT cer.name AS Certificate_Name,

bks.encryptor_thumbprint,

bks.encryptor_type,

bks.database_name

FROM sys.certificates cer

INNER JOIN msdb.dbo.backupsetbks

ON cer.thumbprint = bks.encryptor_thumbprint

If you want to look at all the certificates in the system, just query the DMV.

SELECT * AS Certificate_Name,

FROM sys.certificates

Restoring Headers of backups

We can also restore the headers, but this will never show the backup was encrypted.

RESTORE HEADERONLY

FROM DISK = ‘D:\SQLFiles\Backup\03-mySecureDB_encrypt.bak’

I am sure, this is a good starting point. Next blog post – let me call out some of the other errors that we can get when working with Encrypted backups.

Tags: , , , , , , , , ,

This entry was posted on Wednesday, May 7th, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply