0

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.

1 Answer 1

4

Not sure why the error.

You're just missing the FORMAT option, it should work once you tell it to overwrite the media header, otherwise it'll try to read it, which it can't because that certificate was dropped.

Use this, instead:

BACKUP DATABASE restore_test_encrypted 
TO DISK = N'C:\temp\encrypted_backup.bak'
WITH INIT,
     FORMAT,
     ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = backup_encryption_cert),
     COMPRESSION,
     NAME = 'Encrypted Full Backup',
     STATS = 10;
1
  • Thanks Sean, it worked! Commented Nov 11 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.