Backup Encryption | Part-3

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.

Restore completed without service master key2

 

Thanks,
Sarabpreet Singh

Sarabpreet Singh Anand

SQLServer - MVP Sarabpreet is SQLServer MVP, Consultant, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 11+ years of Experience and worked with Industry Leaders. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”.

You may also like...

Leave a Reply