Encryption for Backups could be a new feature introduced in SQL Server 2014 and therefore the advantages of this feature are:   

  • Encrypting the database backups helps secure the data.
  • Encryption also can be used for databases that are encrypted encryption TDE.
  • Encryption is supported for backups done by SQL Server Managed Backup to Windows Azure, that provides extra security for off-site backups.
  • This feature supports multiple encoding algorithms as well as AES 128, AES 192, AES 256, and Triple DES

You'll be able to integrate encryption keys with Extended Key Management (EKM) providers. The following are pre-requisites for encrypting a backup:
Let’s Create a Database Master Key for the master database.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourpassword@word123';
GO
And then Create a certificate or asymmetric Key to use for backup encryption and write the following code:
Use Master
GO
CREATE CERTIFICATE CertforBackupEncryption
   WITH SUBJECT = 'Certificate for Backup Encryption ';
GO
Then Backup the database with encryption:
BACKUP DATABASE [PeterSQL]
TO DISK = N'C:\Backup\PeterSQL.bak'
WITH
  INIT,
  COMPRESSION,
  ENCRYPTION
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = CertforBackupEncryption
   ),
  STATS = 10
GO

Restoring the encrypted backup:
SQL Server restore doesn't need any encryption parameters to be specified during restores. It will need that the certificate or the asymmetric key used to encrypt the backup file be out there on the instance that you simply are restoring to. The user account performing the restore should have read DEFINITION permissions on the certificate or key. If you're restoring the encrypted backup to a different instance, you must confirm that the certificate is offered on it instance.

SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service for the next 3 days. Once your trial period is complete, you decide whether you'd like to continue.