58638: Acronis Backup: Backing Up SQL Databases with Transparent Data Encryption

Translate to:

Both Acronis Backup 12 and Acronis Backup Advanced 11.7 support SQL databases with Transparent Data Encryption.

Backup

Database backup

In Acronis Backup 12 you can either back up SQL databases by a dedicated backup plan or create a backup of your entire server with enabled application backup.

  • Follow this article to back up SQL databases
  • Follow this article to back up the entire machine with application-aware backup enabled

In Acronis Backup Advanced 11.7 you can back up SQL databases along with the entire machine by using Single-pass backup as described in this article.

Certificate backup

Back up the certificate file containing database encription key as described in this manual. We recommend saving the backup in a remote location, this way you will be able to restore the certificate and thus databases in case of SQL server malfunction or hardware failure.

Restore

To restore SQL databases with Transparent Data Encryption first restore the certificate, and then restore the databases.

Certificate restore

  1. Move or copy the backup of the server certificate and the private key file to the target SLQ server, place it to the same location as on the source server.
  2. Create a database master key on the destination instance of the target SQL Server:
    USE master;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
    GO
    where <password> is the password that is used to encrypt the master key in the database.
    For details see this topic.
  3. Recreate the server certificate by using the certificate backup. The password must be the same as the password used for backup creation.
    CREATE CERTIFICATE TestSQLServerCert
    FROM FILE = 'TestSQLServerCert'
    WITH PRIVATE KEY
    (
    FILE = 'SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = '<password>'
    );
    GO
    For details see this topic.

Database restore

In both Acronis Backup 12 and Acronis Backup Advanced 11.7 you can either restore directly to the target SQL server instance or restore databases as files and attach them to the target SQL server afterwards. To restore directly to the SQL server instance, Agent for SQL should is installed on the machine running the instance.

For detailed instructions see these articles:

You are reporting a typo in the following text:
Simply click the "Send typo report" button to complete the report. You can also include a comment.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
3 + 13 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.