Solved

Encrypted SQL database

  • 18 January 2023
  • 3 replies
  • 642 views

Badge +3

Is it possible to backup and restore SQL databases that are encrypted en secured?

icon

Best answer by Scott Reynolds 19 January 2023, 14:12

View original

3 replies

Userlevel 6
Badge +15

Yes databases that are encrypted can be protected with the SQL iDA.

Userlevel 7
Badge +23

If TDE or Always Encrypted are enabled, then the following rules apply:

  •     Backups are not affected.
  •     If you have a storage policy that uses deduplication, then the deduplication ratio is low or non-existent.
  •     If the restore destination is a different client or instance, then the SQL administrator must back up the encryption key, and present it to the destination instance.
Userlevel 6
Badge +14

Damian’s post highlights everything however I want to make a special note of the third line posted from Damian taken from BOL here

https://documentation.commvault.com/2022e/expert/18208_back_up_sql_server_data.html

The SQL agent does not backup the encryption keys. You must make sure you manage them and import them to any restore destination. DBAs should be aware but adding this for more info from MSFT here:

https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql?view=sql-server-ver16

Reply