Skip to main content

Backup & Restore

Backup and Restore

Backup and restore TDE-encrypted databases across SQL Server instances

Applies to:
SQL Server 2016+TDE Encrypted DBsCross-Server Restore

Overview​

When a database is encrypted with TDE, backup files are also encrypted. To restore these backups, you need access to the same asymmetric key and DuoKey credentials.

Prerequisites

  • Database encrypted with DuoKey EKM
  • SQL Server Management Studio (SSMS) installed
  • Appropriate backup/restore permissions
  • Understanding of TDE backup requirements

Backup Requirements​

To restore TDE-encrypted backups, you need:

Same Asymmetric Key

The key that protected the DEK must be accessible

DuoKey Credentials

Same app credentials as the source server

EKM Provider

Provider must be installed on target server

Network Access

HTTPS connectivity to DuoKey platform

Backup on Primary Server​

1

Verify Database is Encrypted

Check Encryption StatusSQL
SELECT
  DB_NAME(database_id) AS DatabaseName,
  encryption_state,
  CASE encryption_state
      WHEN 3 THEN 'Encrypted - Ready for backup'
      ELSE 'Not ready - State: ' + CAST(encryption_state AS VARCHAR)
  END AS Status
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'EmployeeDB';
GO
Warning

Only proceed with backup when encryption_state = 3 (Encrypted).

2

Create Backup

Full Database BackupSQL
BACKUP DATABASE EmployeeDB
TO DISK = 'C:\Backups\EmployeeDB.bak'
WITH
  COMPRESSION,
  STATS = 10,
  CHECKSUM;
GO
3

Verify Backup

Verify Backup IntegritySQL
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\EmployeeDB.bak'
WITH CHECKSUM;
GO
Tip

Transfer the backup file securely to the secondary server using encrypted channels (SFTP, SCP, or encrypted network share).

Restore on Secondary Server​

Prerequisites for Secondary Server​

Secondary Server Requirements

Same SQL EKM AppOr shared setup file from primary
EKM Provider InstalledAnd properly configured
Same Vault AccessAccess to same DuoKey vault and keys
Network ConnectivityHTTPS to DuoKey platform
1

Configure Secondary Server

Configure the secondary server with the same credentials as the primary:

Configure EKM on SecondarySQL
-- Enable EKM
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;
GO

-- Create cryptographic provider
CREATE CRYPTOGRAPHIC PROVIDER DuoKeyProvider
FROM FILE = 'C:\Program Files\DKE\EKM\DuoKeyCryptoProvider.dll';
GO

-- Create credentials (use same values as primary)
CREATE CREDENTIAL DuoKeyCredential
WITH IDENTITY = '<identity-label>',
SECRET = '<enrolled-agent-key>'
FOR CRYPTOGRAPHIC PROVIDER DuoKeyProvider;
GO

-- Map credentials to login
ALTER LOGIN [sa]
ADD CREDENTIAL DuoKeyCredential;
GO
2

Open the Existing Key

Open the existing asymmetric key from DuoKey:

Open Existing Asymmetric KeySQL
USE master;
CREATE ASYMMETRIC KEY SQL_EKM_Key
FROM PROVIDER DuoKeyProvider
WITH
  PROVIDER_KEY_NAME = 'SQL_EKM_Key',
  CREATION_DISPOSITION = OPEN_EXISTING;
GO
Important

Use CREATION_DISPOSITION = OPEN_EXISTING to open the existing key from DuoKey. Do NOT use CREATE_NEW as this will create a different key.

3

Create Key Credentials

Create Key Login and CredentialsSQL
USE master;
CREATE CREDENTIAL SQL_EKM_Key_Cred
WITH IDENTITY = '<identity-label>',
SECRET = '<enrolled-agent-key>'
FOR CRYPTOGRAPHIC PROVIDER DuoKeyProvider;
GO

CREATE LOGIN SQL_EKM_Key_Login
FROM ASYMMETRIC KEY SQL_EKM_Key;
GO

ALTER LOGIN SQL_EKM_Key_Login
ADD CREDENTIAL SQL_EKM_Key_Cred;
GO
4

Restore the Database

Restore Encrypted DatabaseSQL
RESTORE DATABASE EmployeeDB
FROM DISK = 'C:\Backups\EmployeeDB.bak'
WITH REPLACE;
GO
Tip

The WITH REPLACE option overwrites the existing database if it exists.

5

Verify Restoration

Verify Restored DatabaseSQL
-- Check database status
SELECT
  name,
  state_desc,
  is_encrypted,
  recovery_model_desc
FROM sys.databases
WHERE name = 'EmployeeDB';
GO

-- Verify encryption is intact
SELECT
  DB_NAME(database_id) AS DatabaseName,
  encryption_state,
  percent_complete
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'EmployeeDB';
GO

-- Test data access
USE EmployeeDB;
SELECT TOP 10 * FROM Employees;
GO

Backup Best Practices​

Strategy

Full backups weekly, differential daily, log backups hourly

Storage

Backups are encrypted by TDE; store in secure, access-controlled locations

Off-site

Maintain off-site copies for disaster recovery

Verification

Always verify backups after creation

Automated Backup Job​

Create SQL Agent Backup JobSQL
USE msdb;
GO

EXEC dbo.sp_add_job
  @job_name = N'DailyBackup_EncryptedDBs',
  @enabled = 1;
GO

EXEC dbo.sp_add_jobstep
  @job_name = N'DailyBackup_EncryptedDBs',
  @step_name = N'BackupStep',
  @subsystem = N'TSQL',
  @command = N'
      BACKUP DATABASE [EmployeeDB]
      TO DISK = N''C:\Backups\EmployeeDB_'' +
                CONVERT(VARCHAR(8), GETDATE(), 112) + ''.bak''
      WITH COMPRESSION, CHECKSUM;',
  @retry_attempts = 3,
  @retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
  @schedule_name = N'DailyAt2AM',
  @freq_type = 4, -- Daily
  @freq_interval = 1,
  @active_start_time = 020000; -- 2:00 AM
GO

EXEC dbo.sp_attach_schedule
  @job_name = N'DailyBackup_EncryptedDBs',
  @schedule_name = N'DailyAt2AM';
GO

EXEC dbo.sp_add_jobserver
  @job_name = N'DailyBackup_EncryptedDBs';
GO

Restore Scenarios​

Troubleshooting​

Backup Information Commands​

View Backup ContentsSQL
-- View files in backup
RESTORE FILELISTONLY
FROM DISK = 'C:\Backups\EmployeeDB.bak';
GO

-- View backup metadata
RESTORE HEADERONLY
FROM DISK = 'C:\Backups\EmployeeDB.bak';
GO

Next Steps​

Key Rotation

Rotate encryption keys regularly

Monitor Encryption

Check encryption status and progress

Database Encryption

Enable TDE on more databases

See Also