Restoring Encrypted Databases with Veeam Explorer for Microsoft SQL Server

KB ID: 2006
Product: Veeam Backup & Replication | 8.0 | 9.0 | 9.5 | 10 | 11
Published: 2015-02-10
Last Modified: 2022-05-14
Get weekly article updates
By subscribing, you are agreeing to have your personal information managed in accordance with the terms of Veeam's Privacy Policy.

Cheers for trusting us with the spot in your mailbox!

Now you’re less likely to miss what’s been brewing in our knowledge base with this weekly digest

error icon

Oops! Something went wrong.

Please try again later.

Challenge

Restoring an encrypted database with Veeam Explorer for Microsoft SQL Server fails with one of the following errors:
Cannot find server certificate with thumbprint '<hex code>'
Transparent Data Encryption is not available in the edition of this SQL Server instance.
You are unable to check “Perform restore to the specific transaction” because of this error:
Fine tuning is not available (certificate “<certificate name>” does not exist on the target SQL Server).

Cause

For export scenarios and for restoring to the state before a selected transaction, Veeam Explorer for Microsoft SQL Server uses a staging server. This staging server must support and be able to read the encrypted database. For it to read the encrypted database, you must first restore the certificate protecting the Database Encryption Key. See System Requirements for Veeam Explorer for Microsoft SQL Server for more information on the staging server.

 

Note: Transparent Data Encryption requires the Enterprise edition of Microsoft SQL Server; this also applies to the staging server.

 

If you are not exporting or restoring to a specific transaction, but you are restoring to a different SQL Server instance than the one the database was backed up from, the error means that the restore target SQL Server is unable to read the encrypted database. You must first restore the certificate to the SQL Server before restoring the database.

Solution

Identify Certificate

You can identify the required certificate by the data listed in the error message or by the certificate name and serial number displayed in Veeam Explorer for Microsoft SQL Server:

DB Cert

Backup Original Certificate

To back up the certificate on the original SQL Server, use this query:
(Edit the query and replace the paths, certificate name, and password.)

USE master
BACKUP CERTIFICATE <certificate name> TO FILE = 'path_to_file'
WITH PRIVATE KEY(ENCRYPTION BY PASSWORD='******', FILE='path_to_private_key_file');

Restore Certificate

To restore the certificate to the staging server or to the SQL Server to which you are restoring, use this query:
(Edit the query and replace the paths, certificate name, and password.)

USE master
CREATE CERTIFICATE <certificate name> FROM FILE ='path_to_file'
WITH PRIVATE KEY(FILE='path_to_private_key_file', DECRYPTION BY PASSWORD='******');

More information

Example:

For example, if the Database Info indicates the required certificate name is MyServerCert:

USE master
CREATE CERTIFICATE MyServerCert FROM FILE ='C:\backups\certificate.cer'
WITH PRIVATE KEY(FILE='c:\backups\myservercertprivatekey', DECRYPTION BY PASSWORD='StrongPassword1234!');

Troubleshooting

If restoring the certificate fails with the error:

Please create a master key in the database or open the master key in the session before performing this operation

You must generate a new Master Key with the following query:
(Replace the asterisks with a password of your choice.)

USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******'

If you do not have a backup of the certificate and can no longer back it up on the original SQL Server, you can perform Instant VM Recovery and back up the certificate on the restored VM, then retrieve the certificate and private key files as described in KB1459.

 

If you need to restore the encrypted database files (*.mdf and *.ldf) in a way that bypasses Veeam Explorer for Microsoft SQL Server, you can use Windows File Level Restore.

Click here to send feedback regarding this KB, or suggest content for a new KB.
To report a typo on this page, highlight the typo with your mouse and press CTRL + Enter.

Spelling error in text

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Thank you!

Thank you!

Your feedback has been received and will be reviewed.

Oops! Something went wrong.

Please try again later.

KB Feedback/Suggestion

This form is only for KB Feedback/Suggestions, if you need help with the software open a support case

By submitting, you are agreeing to have your personal information managed in accordance with the terms of Veeam's Privacy Policy.
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Verify your email to continue your product download
We've sent a verification code to:
  • Incorrect verification code. Please try again.
An email with a verification code was just sent to
Didn't receive the code? Click to resend in sec
Didn't receive the code? Click to resend
Thank you!

Thank you!

Your feedback has been received and will be reviewed.

error icon

Oops! Something went wrong.

Please try again later.