Friday, October 17, 2014

Backup Encryption in Microsoft SQL server 2014

Microsoft releases newer version of SQL Server that is SQL server 2014. It is very close to the previous version of SQL Server 2012 release. For many, it is very hard to upgrade the newer version of SQL server because it requires money & resources. However, the newer version of SQL Server has many new features for organizations that can justify the cost and upgrade. The most notable features in SQL server 2014 are In-Memory OLTP, enhancement in Always-on, and enhancement in backup.

In this article, we will learn and concentrate on backup enhancement in SQL server 2014.

SQL server 2014 supports in-built backup encryption for the database. All the previous SQL server versions did not support database backup encryption that required a third party software to accomplish this task. With SQL server 2014, DBAs can encrypt the database backup while creating the backup of database. It can be accomplish by specifying the encryption algorithm.

Benefits of SQL Backup Encryption:
  • It helps in securing the data of SQL server database backup. When you try to create a backup of yourdesired database, SQL server 2014 will provide the option for backup data encryption.
  • It supports multiple encryption algorithms: AES 128, AES 192, AES 256, and DES up to AES 256 bit.
  • It is helpful for all the SQL server databases that are encrypted using TDE.
How to Encrypt SQL Backup: You can encrypt the SQL database backup during backing up the database by following methods:
  1. Maintenance Plan Wizard
  2. Using T-SQL
Maintenance Plan Wizard: It creates maintenance plan that is used by database agent on regular basis to perform the task. You can use it to perform database administrative tasks like integrity check, backup, statistics update, shrink database, cleanup history, rebuilt index, recognize index, and many more,  when you required.

Perform following steps to encrypt SQL database backup with Maintenance plan wizard.
  • Right click on the desired database, you want to create backup.
  • Now select ‘Tasks’ and then ‘Back Up’, a backup dialog box will be open.
  • Right click on the ‘Backup Devices’ and select ‘Back up a Database’.
  • You are on Backup option page, Tick ‘encrypt backup’ as shown in the below image.
  • Now select your desired backup algorithm & certificate key from the drop down list.
  • Click on the ‘OK’ button to accomplish the backup encryption operation.

Using T-SQL:

You can easily encrypt the backup of SQL server database with T-SQL during backup creation. Run below T-SQL command to accomplish the task:

TO DISK = D'\\ServerLocation\Backup\Database-Name_backup.bak'
,ENCRYPTION (Algorithm-Name, SERVER CERTIFICATE = Key or Certificat)
,STATS = 10 GO


It is very important to make a backup copy of your ‘Certificate or Asymmetric key’. During backup restoration or for attaching purpose, certificate or asymmetric key is required. Without it, you can not restore or attach your database on another server.

No comments:

Post a Comment