Backup Encryption in SQL Server 2014 Part 1

Backup database encryption is available in SQL server 2014, In prevision versions third party tools required for encryption.

supported backup encryption algorithms are Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES (3DES).

In my demo Backup Encryption in SQL Server 2014 Part 1 I will cover.

  • Creating of Master Key.
  • Creating of Certificate.
  • Backup database with Encryption.

I will cover key management ,restore encrypted databases in separate articles.

–Create a test database
USE [master]
GO

/****** Object: Database [test4] Script Date: 3/22/2015 10:42:41 PM ******/
CREATE DATABASE [Backupencryption]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’Backupencryption’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\DATA\Backupencryption.mdf’ , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N’Backupencryption_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\DATA\Backupencryption_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

———————–

— Encryption procedure

–Step 1 Check if any certficates available.
SELECT * FROM sys.certificates
— in my sqlserver 2014 it returned 0

–Step 2 Lets Create a Certificate/master key in master database

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Password01!’
GO
CREATE CERTIFICATE Backup_Certficate_mydatabases
WITH SUBJECT = ‘My Production Databases Backup Certficate’
GO

–Step 3 check if certficate is created using sys.certificates dmv
SELECT * FROM sys.certificates

Featured image

— Step 4 Lets backup database with encryption using available alogrithms/certficate

–supported backup encryption algorithms are Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES (3DES).

BACKUP DATABASE [Backupencryption]
TO DISK = ‘C:\backups\Backupencryption_Full_03222015.bak’
WITH ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = Backup_Certficate_mydatabases
),STATS = 10,
COMPRESSION;

i will explain the following warning in part 2.

Featured image

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s