Backing Up a SQL Server Database with Encryption

Encrypted backups are no longer optional in most environments.

If backups leave the server, land on shared storage, or are retained long-term, unencrypted backups are a data leak waiting to happen. SQL Server has supported native backup encryption since SQL Server 2012, but the mechanics still catch DBAs out. usually during a restore, not a backup.

This post shows how to back up a database with encryption safely, and just as importantly, what you must protect or you will lose the ability to restore.

---

What Actually Protects an Encrypted Backup

An encrypted SQL Server backup depends on three things:

1. A database master key (DMK)
2. A certificate stored in master
3. A backup of that certificate and private key

If any one of these is missing when you need to restore, the backup is unusable.

---

Step 1: Create a Database Master Key

Before creating a certificate, the database must have a database master key.

CREATE MASTER KEY
    ENCRYPTION BY PASSWORD = 'Use_A_Strong_Password_Stored_Safely';
GO

Why this matters

• The master key protects certificate private keys
• SQL Server encrypts it using:
  • The password you provide
  • The service master key (by default)

Critical DBA warning

If you lose the master key password and cannot open the key using the service master key (for example after a server rebuild or restore elsewhere), you cannot recover certificates protected by it.

This is not theoretical. This is a real data-loss scenario.

You should verify the encryption state:

SELECT name,
       is_master_key_encrypted_by_server
FROM sys.databases
WHERE name = DB_NAME();

---

Step 2: Create a Backup Encryption Certificate

Certificates used for backup encryption must live in master.

USE master;
GO

CREATE CERTIFICATE Backup_Encryption_Cert
WITH SUBJECT = 'SQL Server Backup Encryption Certificate';
GO

Verify it exists:

SELECT name,
       expiry_date
FROM sys.certificates
WHERE name = 'Backup_Encryption_Cert';

Operational notes

• Certificates expire
• Expiry does not invalidate existing backups
• Losing the certificate does

---

Step 3: Back Up the Database with Encryption

BACKUP DATABASE [Computer_World]
TO DISK = N'C:\Temp_MSSQL_Backups\computer_world_full.bak'
WITH
    COMPRESSION,
    ENCRYPTION
    (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = Backup_Encryption_Cert
    ),
    STATS = 10;
GO

What this does:

• Uses AES-256
• Encrypts the backup using the certificate’s public key
• Requires the certificate’s private key to restore

At this point, the backup is encrypted — but you are not finished.

---

Step 4: Back Up the Certificate and Private Key (Non-Negotiable)

SQL Server will warn you if the certificate has not been backed up. That warning is accurate and urgent.

BACKUP CERTIFICATE Backup_Encryption_Cert
TO FILE = 'C:\SecureLocation\Backup_Encryption_Cert.cer'
WITH PRIVATE KEY
(
    FILE = 'C:\SecureLocation\Backup_Encryption_Cert_PrivateKey.pvk',
    ENCRYPTION BY PASSWORD = 'Another_Strong_Password'
);
GO

If you skip this

• You cannot restore the backup on another server
• You cannot restore after rebuilding the instance
• You cannot recover later

Encrypted backups without certificate backups are a trap, not a safety feature.

---

What Breaks Restores (Common DBA Failures)

These scenarios will break encrypted backup restores:

• Certificate was never backed up
• Certificate backup exists, but:
  • Private key password is lost
  • Files were stored only on the original server
• Database restored to a different instance without restoring the certificate
• Service master key was regenerated and the DMK cannot be opened

This is why encryption must be treated as a system, not a checkbox.