- Veeam Support Knowledge Base
- Restoring Encrypted Databases with Veeam Explorer for Microsoft SQL Server
Restoring Encrypted Databases with Veeam Explorer for Microsoft SQL Server
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
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 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 Standard or 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 cannot read the encrypted database. You must 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:
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.
To report a typo on this page, highlight the typo with your mouse and press CTRL + Enter.
Spelling error in text
Thank you!
Your feedback has been received and will be reviewed.
Oops! Something went wrong.
Please try again later.
You have selected too large block!
Please try select less.
KB Feedback/Suggestion
This form is only for KB Feedback/Suggestions, if you need help with the software open a support case
Thank you!
Your feedback has been received and will be reviewed.