Here is my demo code:
USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
END
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'restore_test_encrypted')
BEGIN
ALTER DATABASE restore_test_encrypted SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE restore_test_encrypted;
PRINT 'Database restore_test_encrypted dropped!';
END;
GO
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'backup_encryption_cert')
BEGIN
PRINT 'Certificate backup_encryption_cert already exists!';
DROP CERTIFICATE backup_encryption_cert;
PRINT 'Certificate backup_encryption_cert dropped!';
END;
GO
CREATE CERTIFICATE backup_encryption_cert
WITH SUBJECT = 'Backup Encryption Certificate';
GO
-- Now create the database with the new certificate
CREATE DATABASE restore_test_encrypted;
GO
USE restore_test_encrypted;
GO
CREATE TABLE sensitive_data (
id INT IDENTITY PRIMARY KEY,
sensitive_info NVARCHAR(100)
);
INSERT INTO sensitive_data (sensitive_info) VALUES ('Secret Data 1'), ('Secret Data 2');
GO
BACKUP DATABASE restore_test_encrypted
TO DISK = N'C:\temp\encrypted_backup.bak'
WITH INIT,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = backup_encryption_cert),
COMPRESSION,
NAME = 'Encrypted Full Backup',
STATS = 10;
GO
When I run it, I got:
Database restore_test_encrypted dropped!
Certificate backup_encryption_cert already exists!
Certificate backup_encryption_cert dropped!
(2 rows affected)
Warning: The certificate used for encrypting the database encryption key has not
been backed up. You should immediately back up the certificate and the private key
associated with the certificate. If the certificate ever becomes unavailable or
if you must restore or attach the database on another server, you must have backups
of both the certificate and the private key or you will not be able to open the
database.
Msg 33111, Level 16, State 3, Line 45
Cannot find server certificate with thumbprint '0xC4A58F994054BA72BCAB96CAC5B519C313ACBB48'.
Msg 3013, Level 16, State 1, Line 45
BACKUP DATABASE is terminating abnormally.
Completion time: 2025-11-11T04:32:00.7463436+08:00
I checked the document at https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-ver17&tabs=local. My demo code is very similar to the example in that page. Not sure why the error. Can anyone help explain a bit? Thanks. Btw, I'm on Windows SQL Server 2016.