A SQL Server database backup/restore issue

Question

A backup/restore issue has taken place on one of our client’s server.

We use Microsoft SQL Server 2014 installed on Microsoft Windows Server 2012.

Problem results: - Critical data loss, inability to restore the database.

Problem details: - We used to perform scheduled ‘encrypted’ backups from the client’s database. The database encryption is implemented using MASTER KEY ENCRYPTION BY PASSWORD.

Using the script:

USE master; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '#W0rrtyldOrtf$$ByryackupP@trSs0rd#';  
GO  
CREATE CERTIFICATE DBBackupEncryptCert  
   WITH SUBJECT = 'DBBackupEncryptCert';  
GO

The problem occurred after a server hardware failure led to a corruption of the server HDD that contains our databases and its backups encryption certificates. Now, we have an encrypted backup of the database, but its certificate was on the damaged HDD and was lost.

We also have a one-week earlier non-encrypted backup of the database. But the one-week lost data is very critical to us and to the client.

Our trials

We tried to recover the damaged HDD physical files, but this option was not possible We have tried to find a way to re-generate the backup certificate using an older backup of the master database by creating an encryption certificate out of it with the same password, but this attempt did not work We thought that if we could somehow re-create the certificate using the old backup, this would help us decrypt the database backup file.

So, kindly I ask you for help. If there is a way we can restore this database using the current encrypted backup without the certificate key, this would be appreciated.

And please, do not hesitate to ask for any further information or resources to let you know more about the issue for resolution.

Thank you

Answer 1

If you have a copy of the master database which holds the DBBackupEncryptCert then you'll want to restore that and grab the copy of the certificate, then import it into your current instance for use.

If there is a way we can restore this database using the current encrypted backup without the certificate key ...

That would defeat the whole purpose of encrypting the backup - so, unfortunately, the answer is that there is no way. There is no back door or gated access to get the data, it is encrypted by the certificate of which no one has a copy but you. That's what makes it safe and secure (well, partially).

We also have a one-week earlier non-encrypted backup of the database.

Without the certificate, this is what you're going to be able to recover.