SQL Backup and Restore ( Encryption Database)

 Backup:

To encrypt a backup you need certificate or an asymmetric key.

Create a Master Key:

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(Password123)'

Create a certificate

CREATE CERTIFICATE BackupCertificate

WITH SUBJECT ='Backup Encryption Certificate'

Create a Backup:

 

 Backup Certificate:

You must save your certificate and private key, without that you will not able to restore the backups:

BACKUP CERTIFICATE BackupCertificate

   TO FILE = 'D:\cert\BackupCertificate.cer' 

   WITH PRIVATE KEY(

      FILE='D:\cert\KeyBackupCertificate.ppk',

      ENCRYPTION BY PASSWORD ='(Password123)'

   )

 Restore:

If you try to restore the database in another server without the certificate you will get this message:

 

 You need to create the master key before restore ( Destination Server):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(Password123)'

Now, you need to create the certificate using the existing certificate and password, in my example is in the cert folder: 

 Create a certificate on the destination server

CREATE CERTIFICATE BackupCertificate

   FROM FILE = 'D:\cert\BackupCertificate.cer'

   WITH PRIVATE KEY(

      FILE ='D:\cert\KeyBackupCertificate.ppk',

      DECRYPTION BY PASSWORD='(Password123)'

   )

Now, I was able to restore: