Help: SQL Server

Before we jump into the new feature, lets have a look at behavior in previous version. Same simulation can be done on SQL Server 2014 as well.

1. Create Database
2. Create table in the database and insert some rows.
3. Take backup of the database and check if we are able to see those rows in the backup.

Here are the commands which we can run to achieve this.

Create database [SQLServerHelp]
go
use [SQLServerHelp]
go
Create Table MyData (i int, j char(100));
go
insert into MyData values (1, 'See this data')
go
backup database SQLServerHelp to Disk = 'E:\Tools\SQLServerHelp.bak'
go

I have opened the backup file with one of the text editor and I can search for the row which I inserted. Note that opening big file in text editors may take a lot of time and file may become unusable for SQL Server. PLEASE DON’T TRY THIS ON PRODCUTION DATABASE BACKUP. I have highlighted the row which was inserted via above script. (1, ‘See this data’)

The way to avoid above situation is that encrypt the database use TDE or encrypt the column using column encryption. Please read below

http://technet.microsoft.com/en-us/library/cc278098(v=SQL.100).aspx (Database Encryption in SQL Server 2008 Enterprise Edition)

There are some overhead involved with above techniques and you may want to make sure that only backup is encrypted and you are OK if MDF file has data in readable format. To solve this, SQL Server 2014 has new feature called backup encryption. You can read more on books online

Let’s look at steps involved.

Create database master key for the master database.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLServer-Help@123';
GO

Create a Backup Certificate

Use Master
GO
CREATE CERTIFICATE BackupEncryptCert
   WITH SUBJECT = 'SQL Backup Encryption Certificate';
GO

Take a backup: This can be done by SSMS UI or T-SQL

T-SQL

BACKUP DATABASE [SQLServerHelp] TO  DISK = N'E:\Tools\Encrypted_SQLServerHelp.bak' WITH NOFORMAT, NOINIT,  
NAME = N'SQLServerHelp-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [BackupEncryptCert])
GO

Here is the message which we can see as output in SSMS when we run T-SQL.

Here is the complete text of highlighted warning.

“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.”

It’s clear that to restore this backup, we need to backup certificate. Going back to original problem, I tried searching for the text in new backup file but can’t find it. If you look at screenshot, it’s clear that it’s more unreadable.

As Vinod (b|t) mentioned in his blog, we can see complete message in ERRORLOG (this is also an enhancement in SQL Server 2014)

2014-04-03 06:36:42.780 Backup       Database backed up. Database: SQLServerHelp, creation date(time): 2014/04/01(10:15:50), pages dumped: 307, first LSN: 34:24:37, last LSN: 34:56:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Tools\SQLServerHelp.bak’}). This is a

2014-04-03 06:36:42.810 Backup       BACKUP DATABASE successfully processed 298 pages in 0.212 seconds (10.981 MB/sec).

2014-04-03 07:25:49.220 Backup       Database backed up. Database: SQLServerHelp, creation date(time): 2014/04/01(10:15:50), pages dumped: 307, first LSN: 34:168:37, last LSN: 34:200:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Tools\Encrypted_SQLServerHelp.bak’}

2014-04-03 07:25:49.240 Backup       BACKUP DATABASE successfully processed 298 pages in 0.228 seconds (10.211 MB/sec).

It’s clear that second one took little more time. I am guessing this is because of encryption as all other factors are same.

Hope you have learned something new today!

Edit (9 April 2014) – Vinod (b|t) wrote a blog on restoring such backups. http://blogs.extremeexperts.com/2014/04/09/sql-server-2014-restoring-encrypted-backups/