https://login.veeam.com/en/oauth?client_id=nXojRrypJ8&redirect_uri=https%3A%2F%2Fwww.veeam.com%2Fservices%2Fauthentication%2Fredirect_url&response_type=code&scope=profile&state=eyJmaW5hbFJlZGlyZWN0TG9jYXRpb24iOiJodHRwczovL3d3dy52ZWVhbS5jb20va2IyMDA2IiwiaGFzaCI6IjFjZDkxMTExLTFmM2UtNGZmZC1hNmU2LTcwZWMwMWM2Yzc0NCJ9
Restoring Encrypted Databases with VESQL
Challenge
Restoring an encrypted database with Veeam Explorer for Microsoft SQL Server fails with one of the following errors:
OR
Fine tuning is not available (certificate “<certificate name>” does not exist on the target SQL Server).
Cannot find server certificate with thumbprint '<hex code>'
Transparent Data Encryption is not available in the edition of this SQL Server instance.
Transparent Data Encryption is not available in the edition of this SQL Server instance.
OR
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 restore to the state before selected transaction, Veeam Explorer for Microsoft SQL Server uses a staging Microsoft SQL Server. This staging server must support and be able to read the encrypted database. In order for it to read the encrypted database, you must first restore the certificate protecting the Database Encryption Key. For more information on the staging server, see System Requirements for Veeam Explorer for Microsoft SQL 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 you backed up from, that SQL Server is indicating that it is unable to read the encrypted database. You must first restore the certificate to the SQL Server before you can restore the database.
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 you backed up from, that SQL Server is indicating that it is unable to read the encrypted database. You must first restore the certificate to the SQL Server before you can restore the database.
Solution
You can identify the required certificate by the data listed in the error message, or by the certificate name and serial number displayed in VESQL:
To back up the certificate on the original SQL Server, use this query (replacing the paths, certificate name, and password):
To restore the certificate on the staging server or on the SQL Server to which you are restoring, use this query (replacing the paths, certificate name, and password):
For example, if the Database Info indicates the required certificate name is MyServerCert:
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 run the following query (replacing the password):
To back up the certificate on the original SQL Server, use this query (replacing the paths, certificate name, and password):
USE masterBACKUP CERTIFICATE <certificate name> TO FILE = 'path_to_file'WITH PRIVATE KEY(ENCRYPTION BY PASSWORD='******', FILE='path_to_private_key_file');To restore the certificate on the staging server or on the SQL Server to which you are restoring, use this query (replacing the paths, certificate name, and password):
USE masterCREATE CERTIFICATE <certificate name> FROM FILE ='path_to_file'WITH PRIVATE KEY(FILE='path_to_private_key_file', DECRYPTION BY PASSWORD='******');For example, if the Database Info indicates the required certificate name is MyServerCert:
USE masterCREATE CERTIFICATE MyServerCert FROM FILE ='C:\backups\certificate.cer'WITH PRIVATE KEY(FILE='c:\backups\myservercertprivatekey', DECRYPTION BY PASSWORD='StrongPassword1234!');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 run the following query (replacing the password):
USE masterCREATE MASTER KEY ENCRYPTION BY PASSWORD = '******'More information
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 VESQL, you can use Windows File Level Restore.
If you need to restore the encrypted database files (*.mdf and *.ldf) in a way that bypasses VESQL, you can use Windows File Level Restore.
KB ID:
2006
Product:
Veeam Backup & Replication
Version:
8.x
Published:
2015-02-10
Last Modified:
2020-08-13
Couldn't find what you were looking for?
Below you can submit an idea for a new knowledge base article.
Report a typo on this page:
Please select a spelling error or a typo on this page with your mouse and press CTRL + Enter to report this mistake to us. Thank you!
Please select a spelling error or a typo on this page with your mouse and press CTRL + Enter to report this mistake to us. Thank you!
Spelling error in text
Knowledge base content request
How can we assist you today?
How can we assist you today?
Happy Holidays!
Chat is offline now. Please fill out the form and we will call you back.
Technical
General and Licensing
Renewal
Sales
Partnership
Do you have an issue or need post-sales technical assistance?
*if you need help choosing the right product, please select "Sales" chat
Do you need guidance on the website, upgrade hints or help with your licenses/accounts?
Questions regarding your Veeam contract renewal? Contact us and learn how you can maximize your savings
Do you need additional license, pricing details, product information or have any other sales-related question?
Would you like to become our partner or do you need assistance as our existing partner?
Start chat
We apologize, but this chat is not available at the moment
Start chat
We apologize, but this chat is not available at the moment
Start chat
We apologize, but this chat is not available at the moment
Start chat
We apologize, but this chat is not available at the moment
Privacy Notice. By submitting, you agree that your personal data will be managed by Veeam in accordance with the Privacy Policy.
Back
Thank you for your request!
Veeam representative will get back to you shortly.
Stay up to date with Veeam product updates, latest news, and recent content.
By subscribing, you agree to receive communications about Veeam products, services and events. Your personal data will be managed by Veeam in accordance with the Privacy Policy. You can unsubscribe at any time.