How to Set Up SQL Server TDE: Setup, Backup, and Restore to Another Server

Transparent Data Encryption (TDE) encrypts your SQL Server database at rest — no application code changes, no schema modifications, no performance-killing stored procedure wrappers. You flip the switch, SQL Server handles the rest.

That's the promise. The reality is a multi-step key hierarchy, a migration process that trips up experienced DBAs, and a few edge cases that don't announce themselves until you're under pressure.

This guide covers both. Part 1 builds TDE from scratch on a single server. Part 2 moves an encrypted database to a new server — the scenario you'll face during DR drills, server migrations, and server refreshes. Everything here works in SQL Server 2016 and later.

What TDE Protects (and What It Doesn't)

TDE encrypts the data files (.mdf, .ndf) and log files (.ldf) while at rest on disk. When the database engine reads pages from disk, they're decrypted in memory transparently.

What TDE does:

• Encrypts data at rest on disk
• Protects .mdf, .ndf, .ldf files
• Encrypts database backups automatically
• Zero code changes required

What TDE does NOT protect:

• Data in transit (use TLS for that)
• Tempdb database (encrypted for ALL databases once ANY database has TDE enabled — performance impact here)
• Memory contents
• Log files that aren't associated with the encrypted database
• Backups taken before TDE was enabled (those are still unencrypted)

This distinction matters. TDE is a disk-encryption layer, not a data-protection comprehensive solution. You still need column-level encryption for sensitive fields, TLS for transit, and row-level security for access control.

Prerequisites

Edition requirements:

• Enterprise Edition: Full TDE on all user databases
• Standard Edition: Partial TDE as of SQL Server 2019 CU5 — can encrypt user databases but not tempdb
• Express/Web: No TDE support

If you're on Standard Edition pre-CU5 and need TDE, plan your upgrade path first.

The Key Hierarchy

TDE isn't a single key — it's a chain. Understanding the hierarchy makes every step below intuitive:

Service Master Key (SMK)     ← auto-created at install, tied to Windows DPAPI
        ↓
Database Master Key (DMK)     ← in master database, protected by SMK
        ↓
Certificate                  ← in master database, protected by DMK
        ↓
Database Encryption Key (DEK) ← in user database, protected by certificate

The SMK is created and managed by SQL Server automatically. Your job starts at the DMK.

Step 1: Create the Database Master Key in master

-- Connect to the master database
USE master;
GO

-- Create the DMK with a strong password
-- This key is protected by the Service Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ngP@ssw0rd!2024';
GO

-- Verify it was created
SELECT name, is_master_key_encrypted_by_server
FROM sys.databases
WHERE name = 'master';
GO

The DMK lives in the master database. Every certificate you create will reference it.

Step 2: Create the Certificate

-- Create the certificate, protected by the DMK
CREATE CERTIFICATE TDECert
    WITH SUBJECT = 'TDE Certificate for ServerEncryption';
GO

-- Verify the certificate
SELECT name, issuer_name, start_date, expiry_date, thumbprint
FROM sys.certificates
WHERE name = 'TDECert';

Now — before you do anything else — back up the certificate public key. This file is safe to copy around. You'll need it on any destination server.

BACKUP CERTIFICATE TDECert
    TO FILE = 'C:\\backups\\tdeserver\\tdessl_cert.cer';
GO

Step 3: Back Up the Private Key (.pvk)

This is the critical file. The .cer file only contains the public key. The .pvk contains the private key — without it, you cannot restore this certificate on another server.

BACKUP CERTIFICATE TDECert
    TO FILE = 'C:\\backups\\tdeserver\\tdessl_cert.pvk'
    WITH PRIVATE KEY (
        FILE = 'C:\\backups\\tdeserver\\tdessl_cert.pvk',
        ENCRYPTION BY PASSWORD = 'Pr1v@teK3y!P@ss'
    );
GO

Store the .pvk file and its password separately. If you lose either, the encrypted database becomes unrecoverable. No exceptions, no recovery mechanism, no Microsoft support ticket that helps.

Store the .pvk on an air-gapped USB drive or in a secrets manager (Azure Key Vault, AWS Secrets Manager, HashiCorp Vault). Never on the database server itself.

Step 4: Create the Database Encryption Key

-- Switch to the target database
USE [YourDatabase];
GO

-- Create the DEK
-- AES_256 is the strongest and recommended algorithm
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

Note the WITH ALGORITHM option. You can choose AES_128, AES_192, or AES_256. Use AES_256 — there's no meaningful performance difference for most workloads, and "we used the strongest option" is a good story to tell auditors.

Step 5: Enable TDE

ALTER DATABASE [YourDatabase]
SET ENCRYPTION ON;
GO

This starts the encryption scan. SQL Server reads every page, encrypts it, and writes it back. For a large database, this takes time — and it generates redo log activity.

Step 6: Verify TDE Is Active

SELECT
    db.name AS DatabaseName,
    db.is_encrypted,
    dek.encryption_state,
    dek.key_algorithm,
    dek.encryption_type,
    dek.percent_complete,
    dek.key_creation_date
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek
    ON db.database_id = dek.database_id
WHERE db.name = 'YourDatabase';

Encryption states explained:

State 3 means your database is encrypted and the process completed cleanly. percent_complete drops to 0 once done.

Part 2 — Migrate and Restore to Another Server

The setup above was the easy part. In production, you almost always need to move an encrypted database — to a DR site, a new server, a refreshed environment. That's where most TDE implementations hit problems.

Source Server — Export Keys

Step 1: Back up the certificate public key (if not already done)

USE master;
GO

BACKUP CERTIFICATE TDECert
    TO FILE = 'C:\\backups\\tdessl_cert.cer';
GO

Step 2: Back up the private key with a password

BACKUP CERTIFICATE TDECert
    TO FILE = 'C:\\backups\\tdessl_cert.pvk'
    WITH PRIVATE KEY (
        FILE = 'C:\\backups\\tdessl_cert.pvk',
        ENCRYPTION BY PASSWORD = 'Pr1v@teK3y!P@ss'
    );
GO

Copy both files to the destination server via a secure channel. Don't email them. Don't put them on a network share.

Destination Server — Import Keys

Step 3: Create the DMK (if it doesn't exist)

USE master;
GO

-- Check if DMK exists first
SELECT is_master_key_encrypted_by_server
FROM sys.databases
WHERE name = 'master';

-- Create if missing
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ngP@ssw0rd!2024';
GO

Step 4: Restore the certificate from the .cer file

CREATE CERTIFICATE TDECert
    FROM FILE = 'C:\\backups\\tdessl_cert.cer';
GO

This uses the public key only. It establishes the certificate identity.

Step 5: Restore the private key from the .pvk

CREATE CERTIFICATE TDECert
    FROM FILE = 'C:\\backups\\tdessl_cert.cer'
    WITH PRIVATE KEY (
        FILE = 'C:\\backups\\tdessl_cert.pvk',
        DECRYPTION BY PASSWORD = 'Pr1v@teK3y!P@ss'
    );
GO

The password must be identical to the one used during the backup. If it isn't, SQL Server returns an error and you need to start the export process again from the source server.

Step 6: Restore the database backup

RESTORE DATABASE [YourDatabase]
    FROM DISK = 'C:\\backups\\final\\backup_full.bak'
    WITH MOVE 'YourDatabase_Data' TO 'E:\\newdata\\backup_final.mdf',
         MOVE 'YourDatabase_Log'  TO 'F:\\newlogs\\backup_final.ldf';
GO

The restore itself is identical to a non-TDE database restore. SQL Server reads the backup, decrypts with the local certificate (which matches the one from the source), and writes the pages to disk.

Step 7: Verify TDE is active on the restored database

SELECT
    db.name AS DatabaseName,
    db.is_encrypted,
    dek.encryption_state,
    dek.key_algorithm
FROM sys.databases db
JOIN sys.dm_database_encryption_keys dek
    ON db.database_id = dek.database_id
WHERE db.name = 'YourDatabase';

State 3 and you're done. The restored database is fully encrypted.

Common Error: "Cannot find server certificate with thumbprint"

This is the most common error during TDE migration. The message reads something like:

Cannot find server certificate with thumbprint 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'.

What it means: The database backup was created on a server with certificate X. The destination server has certificate Y. They don't match.

Causes:

1. The .cer or .pvk files weren't copied to the destination server
2. The certificate was restored but the private key wasn't included (missing DECRYPTION BY PASSWORD clause)
3. The wrong password was used when restoring the .pvk
4. The DMK doesn't exist or wasn't opened — the certificate can't be decrypted

Fixes:

-- Check what certificates exist on destination
SELECT name, thumbprint, pvt_key_encryption_type_desc
FROM sys.certificates;

-- If you need to drop a bad certificate and try again
DROP CERTIFICATE TDECert;

-- Re-restore with the full command including private key
CREATE CERTIFICATE TDECert
    FROM FILE = 'C:\\backups\\tdessl_cert.cer'
    WITH PRIVATE KEY (
        FILE = 'C:\\backups\\tdessl_cert.pvk',
        DECRYPTION BY PASSWORD = 'Pr1v@teK3y!P@ss'
    );

If none of this works, you need to go back to the source server and re-export the keys. There is no workaround.

Performance Impact of TDE

TDE isn't free. Here's what you actually pay:

For most OLTP workloads, TDE overhead is negligible on modern hardware (2016+ servers with AES-NI). Your mileage varies with write-heavy workloads — monitor log write latency after enabling TDE.

TDE with Always On Availability Groups

When TDE meets Always On, certificate management becomes ongoing infrastructure:

On the primary replica: The certificate and DMK are in the master database. Every secondary replica needs the same certificate to decrypt the log blocks it receives during synchronization.

To add a replica to an AG with TDE:

1. Export the certificate and private key from the primary
2. Copy to the new replica (secure channel)
3. Create the DMK on the new replica
4. Restore the certificate with private key on the new replica
5. Add the replica to the AG — SQL Server handles the rest

Rotation requirements: If you rotate your TDE certificate, every replica in the AG must receive the new certificate before the rotation completes. Plan this during a maintenance window.

TDE with Log Shipping

Log shipping with TDE works — but with a wrinkle:

• The log backup file format is encrypted on the source
• The secondary server needs the same certificate to restore those log backups
• If the secondary doesn't have the certificate, log restores fail with the thumbprint error described above

Best practice: Store the certificate in a shared location or deploy it to the secondary during the log shipping configuration. Don't let a missing certificate become your failover failure point.

Backup Encryption — Add a Second Layer

TDE encrypts data at rest on disk and in backups automatically. But backup encryption adds another layer — you control the algorithm, key, and retention independently.

BACKUP DATABASE [YourDatabase]
    TO DISK = 'C:\\backups\\tdessl_full_encrypted.bak'
    WITH
        COMPRESSION,
        ENCRYPTION (
            ALGORITHM = AES_256,
            SERVER CERTIFICATE = TDECert
        );
GO

Why both? TDE protects the database on disk. Backup encryption protects the backup file even if someone steals the backup tape or gains access to the backup repository. It's defense-in-depth, and it matters in regulated environments.

Security Hygiene Checklist

Before you ship this into production:

• [ ] .pvk file stored in a secrets manager, NOT on the database server
• [ ] Certificate password stored separately from the .pvk file
• [ ] DMK backup taken and stored in the same secure location as the .pvk
• [ ] Certificate public key (.cer) backed up and copyable to new servers
• [ ] Certificate rotation schedule documented (recommend annual rotation)
• [ ] Disaster recovery plan includes key restoration steps (test it)
• [ ] Log shipping and AG replicas have the certificate deployed
• [ ] tempdb performance monitored after TDE enablement

Looking to Validate Your Database Configuration?

TDE is just one layer of a secure, performant SQL Server environment. If you're not sure whether your encryption, backups, or disaster recovery setup is production-ready, we offer a free database health assessment.

Book Your Free Database Health Assessment