Encrypting backups and restoring encrypted backups

Your applications cannot be survived without database backups!

Database backup is a fundamental requirement and it is the key task among maintenance tasks. It protects both application and data against almost all failures and it is our duty to protect it. Not only that, it should be well secured because it holds all information related to your organization, hence you should take all possible actions for protecting it from unauthorized access. One method we use for protecting our backups is using an off-site storage, making sure that we have them at then event of a disaster. In addition to that, it can be more secured applying encryption. This post speaks about encrypting SQL Server database backups and restoring them.

SQ:L Server backup encryption uses algorithms such as AES 128, AES 192, AES 256, and Triple DES. And when encrypting, it needs either a certificate or asymmetric key for encrypting data. Here are the steps for preparing the instance and taking a backup.

1. Create the database master key in the master database if it is not created before. This key is a symmetric key and it will be used for protecting all other keys and certificates.
   
   

   -- creating the master key if not exist
   USE master;
   GO
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
   GO
   

2. Backup the master key created and keep it in a secure location. This is required if the database going to be restored to a different instance.
   
   

   -- Back up the database master key
   OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd';
   BACKUP MASTER KEY TO FILE = 'D:\SecuredLocation\master.key'
    ENCRYPTION BY PASSWORD = 'Ma$terPa$$w0rd';
   GO
   

3. Create a certificate or asymmetric key for encrypting backups.
   
   

   -- Create a certificate
   CREATE CERTIFICATE AdventureWorksBackupCertificate
    WITH SUBJECT = 'Backup Encryption Certificate';
   GO
   

4. Backup the certificate or asymmetric key and keep it in a secure location. This is required if the database going to be restored to a different instance.
   
   

   BACKUP CERTIFICATE AdventureWorksBackupCertificate 
   TO FILE = 'D:\SecuredLocation\AdventureWorksBackupCertificate.cer'
   WITH PRIVATE KEY ( FILE = 'D:\SecuredLocation\AdventureWorksBackupCertificate.key' ,
        ENCRYPTION BY PASSWORD = 'CertificatePa$$w0rd');
   GO
   

5. Perform the back up operation using the certificate or asymmetric key created, mentioning the algorithm for encryption.
   
   

   -- Take the initial backup
   BACKUP DATABASE AdventureWorks2014
    TO DISK = 'D:\BackupLocation\AdventureWorks2014.bak'
   WITH INIT, ENCRYPTION( ALGORITHM = AES_128,
     SERVER CERTIFICATE = AdventureWorksBackupCertificate)
   ;
   GO
   

Here are the steps for restoring the backup to the same instance.

1. Restore the backup just like restoring non-encrypted backup. No special steps need to perform as long as master database key and certificate are exist.
   
   

   -- Same Server - restore as a normal backup
   RESTORE DATABASE AdventureWorks2014
    FROM DISK = 'D:\BackupLocation\AdventureWorks2014.bak'
   WITH REPLACE, NORECOVERY;
   GO
   

Here are the steps for restoring the backup to a different instance.

1. Create a master key if it is not exist. This does not need to be the same. You can use the backup of the master key taken if it is a recovery from a complete failure.
   
   

   -- create the master key if not exist
   USE master;
   GO
   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
   

2. Create the certificate or asymmetric key from the backup taken from original instance. Name should be same.
   
   

   -- create the certificate in the new instance
   -- from backup files
   CREATE CERTIFICATE AdventureWorksBackupCertificate
   FROM FILE = 'D:\NewServerSecuredLocation\AdventureWorksBackupCertificate.cer'
   WITH PRIVATE KEY (
   DECRYPTION BY PASSWORD = 'CertificatePa$$w0rd',
   FILE = 'D:\NewServerSecuredLocation\AdventureWorksBackupCertificate.key');
   GO
   

3. Perform the restoration as a normal restoration.
   
   

   RESTORE DATABASE AdventureWorks2014
    FROM DISK = 'D:\NewServerBackupLocation\AdventureWorks2014.bak';
   GO