SQL Server 2014 CTP2(All new,shiny shiny !) availability was announced yesterday at #SQLPASS Summit.

Native backup encryption is one of the key features which was announced with this release, and I’m sure this feature will be widely used.

Native backup encryption will encrypt the data while creating the backup, and eventually you will end up creating an encrypted backup file. This is one of those features which was only provided by 3rd party tools [Similar to backup encryption].

Now backup encryption is out of the box for SQLServer 2014 !

So,what all are the pre-requisites to get started with this feature -

1.  You will need either Standard,Enterprise or BI edition of SQLServer 2014.

2. You will need to have a Certificate or a Asymmetric key.

3. You will need to choose the required encryption algorithm.

As we now know the pre-requisites ,lets try to create an encrypted backup and follow the process one by one.

First we will create a master key -

-- Creates a database master key. 
-- The key is encrypted using the password "Pa55word"
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa55word';
GO

 Followed by creating a Certificate

--Creates a certificate.
Use Master
GO
CREATE CERTIFICATE BackupCertificate
 WITH SUBJECT = 'Backup Encryption Certificate';
GO

Once all the keys/certificate requirements are taken care, we will proceed to create an encrypted backup file by specifying the certificate and a backup algorithm. The syntax is pretty simple -

--Create an encrypted backup file.
BACKUP DATABASE RockStar TO DISK =
'C:\SQL2014CTP2Backup\RockStar_Encrypted.bak'
WITH COMPRESSION,
 ENCRYPTION 
 (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCertificate);

That’s it ! Now we have an encrypted backup file and the result after running the above statement is   -

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Processed 296 pages for database ‘RockStar’, file ‘RockStar’ on file 1.

Processed 2 pages for database ‘RockStar’, file ‘RockStar_log’ on file 1.

BACKUP DATABASE successfully processed 298 pages in 0.045 seconds (51.573 MB/sec).

You might have noticed the warning, it tells you that the certificate is not backed up. Its always recommend to backup the certificate as soon as you create it. [Be safe always !]

Also the algorithm which I mentioned in the above query, ie AES_256 is one among the 4 options available. The rest 3 are  -

AES 128, AES 192, and Triple DES

That’s a simple and straight forward way for creating an encrypted backup file using T-SQL.

GUI also offers this ability and the encryption options are available in the backup options.

Conclusion  - 

Native backup encryption is certainly a good to have feature and I’m looking forward to test this really well during the next few days.

Thanks for reading and keep watching this space for more.