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.
-- 0- status before encryption
SELECT db_name(database_id), EncryptionState =
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.'
-- 1- Creating database master key (DMK)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ng79Pa55w0rd';
-- 2- Create new certificate to protect DEK.
CREATE CERTIFICATE SQLTDECert WITH SUBJECT = 'SQL Server TDE Certificate';
-- 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');
-- 4- Create the database encryption key(DEK) encrypted with server certificate
DECLARE @dbKeyCreationStmt varchar(500);
DECLARE @selectedUserDatabases varchar(500);
set @dbKeyCreationStmt = '
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SQLTDECert '
DECLARE UserDatabases_CTE_Cursor Cursor
-- Selecting user database names.
select name as DatabaseName
where ([dbid] > 4) and ([name] not like '$')
DECLARE @dbName varchar(100);
DECLARE @encryptionStmt varchar(500);
Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
While (@@FETCH_STATUS <> -1)
-- 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
-- Execute Encryption script
EXEC (' USE [' + @dbName+'] '+@dbKeyCreationStmt);
-- Get next database
-- 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.
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.'