Below blog post provides TSQL script that encrypts all user databases, please note:

This posting is provided "AS IS" with no warranties, and confers no rights. 

        

References

            1.     http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx#_Toc18938467
            2.     http://msdn.microsoft.com/en-us/library/bb934049.aspx

                     

          

-- 0- status before encryption

 

SELECT db_name(database_id), EncryptionState =

CASE encryption_state

WHEN 0 THEN 'No database encryption key present, no encryption'

WHEN 1 THEN 'Unencrypted'

WHEN 2 THEN 'Encryption in progress'

WHEN 3 THEN 'Encrypted'

WHEN 4 THEN 'Key change in progress'

WHEN 5 THEN 'Key change in progress'

WHEN 6 THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.'

ELSE 'Un-recognized encryption status.'

END

FROM sys.dm_database_encryption_keys

 

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 1- Creating database master key (DMK)

Use master

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ng79Pa55w0rd';

GO

 

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 2-  Create new certificate to protect DEK.

CREATE CERTIFICATE SQLTDECert WITH SUBJECT = 'SQL Server TDE Certificate';

GO

 

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 3- Create backup of the certificate with private key

BACKUP CERTIFICATE SQLTDECert TO FILE = 'c:\CertificateBackup\cert.bakup'

 

WITH PRIVATE KEY (

 

FILE = 'c:\CertificateBackup\cert_Private_Key.bakup',

 

ENCRYPTION BY PASSWORD = 'Cert79Passw0rd');

 

GO

 

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 4- Create the database encryption key(DEK) encrypted with server certificate

DECLARE @dbKeyCreationStmt varchar(500);

DECLARE @selectedUserDatabases varchar(500);

@selectedUserDatabases

 

set @dbKeyCreationStmt = '

CREATE DATABASE ENCRYPTION KEY

 

WITH ALGORITHM = AES_256

 

ENCRYPTION BY SERVER CERTIFICATE SQLTDECert '

 

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

 

-- Selecting user database names.

select name as DatabaseName

from sysdatabases

where ([dbid] > 4) and ([name] not like '$')

 

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @encryptionStmt varchar(500);

 

 

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

 

BEGIN

 

 

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 5- loop through userdatabases and start background thread that does encryption

 

 

 

-- Encryption SQL statment

set @encryptionStmt =   'ALTER DATABASE [' + @dbName + '] SET ENCRYPTION ON '

 

-- Print SQL statement

print @dbKeyCreationStmt

print @encryptionStmt

 

-- Execute Encryption script

EXEC (' USE [' + @dbName+'] '+@dbKeyCreationStmt);

EXEC (@encryptionStmt)

 

 

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

 

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO

 

-- Get database states during encryption

-- From http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx#_Toc189384673

-- When TDE is enabled (or disabled), the database is marked as encrypted in the sys.databases catalog view

-- and the DEK state is set to Encryption In Progress. The server starts a background thread

-- (called the encryption scan or scan) that scans all database files and encrypts them

-- (or decrypts them if you are disabling TDE).

-- While the DDL executes, an update lock is taken on the database.

-- The encryption scan, which runs asynchronously to the DDL, takes a shared lock.

-- All normal operations that do not conflict with these locks can proceed. Excluded operations include modifying the file structure

-- and detaching the database. While normal database writes to disk from the buffer pool are encrypted,

--  log file writes may not be. The scan also forces a rollover for the virtual log file (VLF) to ensure

-- that future writes to the log are encrypted.

SELECT db_name(database_id), EncryptionState =

CASE encryption_state

WHEN 0 THEN 'No database encryption key present, no encryption'

WHEN 1 THEN 'Unencrypted'

WHEN 2 THEN 'Encryption in progress'

WHEN 3 THEN 'Encrypted'

WHEN 4 THEN 'Key change in progress'

WHEN 5 THEN 'Key change in progress'

WHEN 6 THEN 'Decryption in progress'

ELSE 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.'

END

FROM sys.dm_database_encryption_keys