You’ve been able to encrypt data in SQL Server for several versions using Transparent Data Encryption and, if you backup encrypted data, the backup is encrypted. The downside of this approach is that Transparent Data Encryption has a performance hit and so there was no straightforward method to have an encrypted backup of non-encrypted data. This meant that if you didn’t want your data encrypted then your backup, possibly held off-site, could be restored to another server and your data would be compromised.
SQL Server 2014 introduces encrypted backups and they’re very easy to implement:
1. First, create a certificate and keep it very safe. The following code demonstrates how to create a certificate (use your own name and password):
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘VeryStr0ngP@ssw0rd’
CREATE CERTIFICATE BackupEncryptionCertificate
WITH SUBJECT = ‘This is a certificate to encrypt backups’
2. Now encrypt your backups. You can use the WITH ENCRYPTION clause like this:
4. Or you can specify encryption graphically like this:
Note that it must be a new backup set and cannot be added to an existing set.
Now you have an encrypted backup without having the performance hit of encrypted data.