SQL Server Encrypted Backup and Restore

by

SQL Server encrypted backup is a feature of both standard and enterprise edition in SQL Server 2019. You can refer below link for more details:

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver15

Taking encrypted backups and restoring those backups on another SQL instance requires some additional steps to be followed.

Take Encrypted Backup

USE MASTER;
GO
-- create master key and certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Encrypt@2023';
GO

CREATE CERTIFICATE EncryptBackup
    WITH SUBJECT = 'Backup Encryption Certificate';
GO

-- Take backup of certificate to a file, keep password in repositry
BACKUP CERTIFICATE EncryptBackup TO FILE = 'D:\EncryptBackup.cert'
WITH PRIVATE KEY (
FILE = 'D:\EncryptBackup.key',
ENCRYPTION BY PASSWORD = 'Encrypt@2023')

-- backup the database using encryption and certificate. You can use different algorithm as well here 
BACKUP DATABASE Test
TO DISK = 'D:\Test.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = EncryptBackup)

Above encrypted backups could be restored on same SQL instance without any issues. However if encrypted backups need to be restored on another instance then keys and certificated need to be created on target instance before running restore script.

Restore Encrypted Backup

-- create master key and certificate on target instance
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Encrypt@2023';
GO

-- restore the certificate
CREATE CERTIFICATE EncryptBackup
FROM FILE = 'D:\EncryptBackup.cert'
WITH PRIVATE KEY (FILE = 'D:\EncryptBackup.key',
DECRYPTION BY PASSWORD = 'Encrypt@2023');
GO

--Now run restore , it will work without any issues
Post Views: 55