Database Backup Encryption & Recovery

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  April 4, 2023 at 7:54 pm

  #4169134

  What happens if a server "Dies" and a new server needs to be rebuilt, using encrypted backups ?

  If I create a master key

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master key password>'

  Then create a certificate

  CREATE CERTIFICATE MyTestDBBackupEncryptCert  WITH SUBJECT = 'MyTestDB Backup Encryption Certificate'

  Then backup my database  WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyTestDBBackupEncryptCert )

  So all my backups are encrypted, what is needed to ensure I can restore them to a new server in the event of disaster to the original server ?

  Does the certificate need to be backed up and stored "off site" , then restored on the new server ?

  • This topic was modified 1 year, 8 months ago by  homebrew01.
  • This topic was modified 1 year, 8 months ago by  homebrew01.
  • This topic was modified 1 year, 8 months ago by  homebrew01.

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  April 5, 2023 at 1:26 pm

  #4169500

  You need to backup the certificate. Microsoft lays it out right here. 

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning