Organizations can encrypt data while making backups in SQL Server 2014. In the past, backup encryption could be accomplished using third-party technologies or transparent data encryption (TDE), a SQLfeature. Third-party tools are costly, and TDE encrypts the entire database, not just the backup. Organizations and customers have been requesting native encryption for backups in SQL Server for years. This is undoubtedly one of the most eagerly anticipated features.

Whether we create the backup on-premises or via Windows Azure, encrypted backups are supported.

Supported encryption options
Encryption Algorithm: AES 128, AES 192, AES 256, and Triple DES
Encryptor: A certificate or asymmetric Key

Benefits of Backups Encryption
  1. Encrypting the backups helps secure data.
  2. TDE-encrypted databases can also be encrypted with encryption.
  3. Encryption is supported for SQL Server Backup to Microsoft Azure backups, which adds another layer of protection to off-site backups.
  4. You can use Extended Key Management (EKM) providers to combine encryption keys.
Follow these steps to encrypt a database backup using a Certificate:

1. Create a Database Master Key

USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test123$'

2. Create a Certificate

CREATE CERTIFICATE BackupCertificate
WITH SUBJECT ='Backup Encryption Certificate'

3. Create a CERTIFICATE key with the private key

BACKUP CERTIFICATE BackupCertificate
TO FILE = '\\NODE1\Shared\BackupCertificate.cer'
WITH PRIVATE KEY(
FILE='\\NODE1\Shared\BackupCertificate.ppk',
ENCRYPTION BY PASSWORD ='Test123$')

4. Take backup with [WITH ENCRYPTION] option

BACKUP DATABASE TorDB
TO DISK = '\\NODE1\Shared\TorDB.bak' WITH ENCRYPTION
(ALGORITHM = AES_256, SERVER CERTIFICATE BackupCertificate)

Now we have an encrypted backup, let’s try to restore the same backup on another SQL server instance.

5. Create a Database Master Key

USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test123$'

6. Restore CERTIFICATE key with private key

CREATE CERTIFICATE BackupCertificate
FROM FILE = '\\NODE1\Shared\BackupCertificate.cer'
WITH PRIVATE KEY(
FILE ='\\NODE1\Shared\BackupCertificate.ppk',
DECRYPTION BY PASSWORD='Test123$')

7. Restore DATABASE

RESTORE DATABASE TorDB
FROM DISK = N'\\NODE1\Shared\TorDB.bak', Replace

SQL Server versions before 2014 cannot read encrypted backups*