19 May

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.
FROM FILE = ‘C:\backup\SQL_Prod_Repro_F.key’
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.
DECRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’


–Create the certificate
FROM FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
                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


Sarabpreet Singh

About Author:

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”.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.