Moving a TDE-Encrypted Database to Another SQL Server

The One Rule That Applies to Every Method

Whether you move the database with BACKUP/RESTORE, detach/copy/attach, log shipping, or an availability group, the destination instance needs the certificate protecting the database encryption key before the database arrives, not after. The certificate lives in the source instance's master database; it travels nowhere on its own.

Step 1: Identify What You're Dealing With

Before touching the destination, confirm which certificate protects the database and get its thumbprint, on the source:

SELECT
    DB_NAME(dek.database_id) AS database_name,
    dek.encryption_state,
    c.name AS certificate_name,
    c.thumbprint,
    c.expiry_date
FROM sys.dm_database_encryption_keys dek
JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
WHERE DB_NAME(dek.database_id) = 'ProductionDB';

Check the expiry date while you're there. A certificate that's fine today but expires next month is a problem you want to find now, not mid-migration.

Step 2: Export the Certificate from the Source

USE master;
BACKUP CERTIFICATE TDE_Cert
    TO FILE = 'D:\Secure\TDE_Cert.cer'
    WITH PRIVATE KEY (
        FILE = 'D:\Secure\TDE_Cert.pvk',
        ENCRYPTION BY PASSWORD = 'A strong, different password for the private key file'
    );

If the certificate was already backed up when TDE was first enabled (as it should have been), you may already have both files in your certificate archive and can skip straight to step 3.

Step 3: Import the Certificate on the Destination

The destination instance needs its own database master key before it can hold the certificate. It does not need to match the source's master key or Service Master Key; a fresh one is fine, since the master key only protects the certificate locally on that instance.

USE master;

-- Create a master key if this instance doesn't already have one
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master key password on the destination instance';

CREATE CERTIFICATE TDE_Cert
    FROM FILE = 'D:\Secure\TDE_Cert.cer'
    WITH PRIVATE KEY (
        FILE = 'D:\Secure\TDE_Cert.pvk',
        DECRYPTION BY PASSWORD = 'The password used when backing up the private key'
    );

Confirm the thumbprint matches the source before going any further:

SELECT name, thumbprint FROM sys.certificates WHERE name = 'TDE_Cert';

Step 4: Move the Database Itself

With the certificate in place, any of the standard move methods works exactly as it would for an unencrypted database:

Backup and restore (the usual choice)

-- On the destination, after step 3
RESTORE DATABASE ProductionDB
    FROM DISK = 'D:\Backups\ProductionDB.bak'
    WITH MOVE 'ProductionDB' TO 'D:\Data\ProductionDB.mdf',
         MOVE 'ProductionDB_log' TO 'L:\Log\ProductionDB_log.ldf';

Detach / copy / attach

Because the data and log files are already encrypted at rest, copying the raw files is just as valid as a backup, and can be faster for very large databases when both instances can see the same storage. The certificate requirement is identical either way: attach fails with the same missing-certificate error if step 3 was skipped.

-- On the source
ALTER DATABASE ProductionDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'ProductionDB';
-- copy the .mdf/.ldf files to the destination's storage

-- On the destination, after step 3
CREATE DATABASE ProductionDB
    ON (FILENAME = 'D:\Data\ProductionDB.mdf'),
       (FILENAME = 'L:\Log\ProductionDB_log.ldf')
    FOR ATTACH;

Log shipping or an Availability Group

Both are just a continuous version of the same requirement: every secondary/destination needs the certificate before the first log record referencing the encrypted data arrives, not after. See the companion article on enabling TDE in an AlwaysOn Availability Group for the AG-specific sequence, including what adding a new replica later requires.

Cross-Version Moves

The certificate requirement is independent of, and in addition to, the normal SQL Server rule that a backup can only be restored to the same or a later version, never an older one. Moving an encrypted database to an older engine version fails for the same reason an unencrypted one would; TDE does not add a second version constraint on top, but it doesn't waive the existing one either.

If You Genuinely Cannot Move the Certificate

Occasionally a certificate is unrecoverable, or organizational policy blocks moving key material between environments (a common ask when moving from a customer's production estate into an isolated test environment). The only remaining path is to decrypt on the source before the move and re-encrypt on the destination afterward:

-- On the source, before the move
ALTER DATABASE ProductionDB SET ENCRYPTION OFF;
-- wait for decryption_state = 0 in sys.dm_database_encryption_keys, then move the plaintext database

-- On the destination, after the move, if it should stay encrypted there too
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
ALTER DATABASE ProductionDB SET ENCRYPTION ON;

This costs two full-table scans, one to decrypt and one to re-encrypt, on top of the move itself. On a genuinely large database that's a meaningful chunk of maintenance-window time for what is, in every other case, an unnecessary detour. Treat it as the fallback for when the certificate truly cannot travel, not a default choice.

Verifying After the Move

SELECT
    DB_NAME(dek.database_id) AS database_name,
    dek.encryption_state,        -- 3 = encrypted
    dek.percent_complete,
    c.thumbprint
FROM sys.dm_database_encryption_keys dek
JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
WHERE DB_NAME(dek.database_id) = 'ProductionDB';

Then do the one check that actually matters beyond the DMV: take a fresh backup on the destination and restore it, ideally onto a third, disposable instance holding only the certificate. A migration isn't finished until you've proven the destination can restore its own backups independently of the source.

Checklist

The Bottom Line

Moving a TDE-encrypted database is not harder than moving a plain one, it just has an unskippable prerequisite: the certificate has to be on the destination first, regardless of which of backup/restore, detach/attach, log shipping, or an AG you use to physically move the data. Get the certificate there, confirm the thumbprint matches, and every other part of the move behaves exactly the way it would without encryption in the picture at all.