Transparent Data Encryption (TDE)

Transparent Data Encryption (or TDE) is a means for encrypting your entire database, transparently. It was introduced from SQL Server 2008. It is transparent to the applications, as SQL Server does the encryption / decryption of data on the fly as required, there is no additional configuration required for your applications to make use of a database encrypted using TDE. Once TDE is set up, the data will be encrypted on the disk, and any backups of the data will be encrypted also, and you'll be unable to restore the data without the encyption key.

Setting up TDE:
There are a few steps to set up TDE for your SQL Server database(s):

1. Create a Master Key (on the master database)
2. Create a Certificate with the Master Key (on the master database, and back this certificate up)
3. Create Database Encryption Key (on the database you want to use TDE on)
4. Turn on encryption on the required database
5. Repeat steps 3 & 4 for any other databases you require TDE for

SQL for these steps (taken from MSDN):

use master;

go

create master key encryption by password = '<UseStrongPasswordHere>';

go

create certificate MyServerCert

with

       subject = 'My DEK Certificate';

go

use AdventureWorks2012;

go

create database encryption key

with algorithm = aes_128

encryption by server certificate MyServerCert;

go

alter database AdventureWorks2012 set encryption on;

go

Moving a TDE Encrypted database:

If a database using TDE is required to be restored to another server, the following steps must be followed:

1. Via Windows Explorer, copy TDE encrypted database, backup of server key and certificate to destination server
2. Create database master key on destination server
3. Recreate server certificate on destination server using backup from origin server
4. Restore / attach TDE encypted database

Resources:

Recover database without database encryption key:
http://dba.stackexchange.com/questions/34753/can-i-recover-a-tde-certificate-by-restoring-the-master-database

MSDN - set up / move TDE encrypted database: https://msdn.microsoft.com/en-gb/library/ff773063.aspx