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.