Backup Encryption | Part-3

May 19, 2014 Sarabpreet Singh Anand

In Part-1 we discussed the feature of backup encryption and part-2 walks us through the scripts to prepare for the pre-requisites and take an encrypted backup, In part-2 we took the backup of Repro_FGDN Database on MBTEST server.

We took the encrypted backup using Certificate as an encryptor and used AES_256 encryption algorithm.

In this part we’ll try and restore the same encrypted backup on another server – FGDNPROD.

–Before you restore the encrypted backup on the destination server, you must create the Encryptor; in part-2 we created a certificate which was used as an encryptor. The certificate was encrypted with a DMK so we must first restore the DMK before creating the certificate.
RESTORE MASTER KEY
FROM FILE = ‘C:\backup\SQL_Prod_Repro_F.key’
DECRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b’
ENCRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’;

–You must open the master key before using the DMK, once the Database Master Key is open – it stays open for the complete session.
OPEN MASTER KEY
DECRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’

–Create the certificate
CREATE CERTIFICATE FGDN_DMK
FROM FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
WITH PRIVATE KEY
        (
                FILE = ‘C:\backup\FGDN_DMK_certificate_private_key.key’
                , DECRYPTION BY PASSWORD = ‘abcalseImRtjSnuEiheYf8b’
        )

–Closing the DMK
close master key

Restoring service master key on destination server is not required.

–Restore encrypted Database backup
Restore database repro_FGDN
from disk =’C:\backup\Repro_FGDN_22.bak’

Finally after creation of the encryptor you will be able to restore the encrypted backup, refer the screenshot where I was able to restore the encrypted backup on another server named FGDNPROD.

Thanks,
Sarabpreet Singh