SQL Server Backup Encryption

To encrypt a backup we need to configure an encryption algorithm (supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES) and an encryptor (a certificate or asymmetric key). So if you don’t have a Database Master Key defined you need to create one.

Asymmetric keys used to encrypt backups must be from a cryptographic provider in Extensible Key Management.

Encrypted backups cannot be appended to an existing backup set. An encrypted backup can be restored on another server as long as the server has the appropriate certificate.

You should perform a backup of the encryption certificate and keys then keep them safe because an encrypted backup cannot be restored without the certificate used for encryption

Simple Example:

CREATE DATABASE SampleDB
GO

— in order to create an encrypted backup we must create a Database Master Key.

USE SampleDB GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘my_encryption_password’;
GO

– – create a certificate to act as an encryptor.

Use SampleDB
GO

CREATE CERTIFICATE SampleDB_Backup_Certificate
WITH SUBJECT = ‘MySubject’;
GO

 

encr_backup
encr_backup
encr_backup
encr_backup
encr_backup
encr_backup

— or with T-SQL

USE master
GO

BACKUP DATABASE [SampleDB] TO DISK = N’C:\MyPath\SampleDB.bak’
WITH FORMAT, INIT, SKIP, ENCRYPTION (
ALGORITHM = AES_256, — { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
SERVER CERTIFICATE = [SampleDB_Backup_Certificate]
);
GO

— To use an asymmetric key instead of a certificate the statement is as follows.

USE master
GO

BACKUP DATABASE [SampleDB] TO DISK = N’C:\MyPath\SampleDB.bak’
WITH FORMAT,INIT,SKIP,ENCRYPTION (
ALGORITHM = AES_256, — { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
SERVER ASYMMETRIC KEY = [Key_Name]
);
GO

Restoring an Encrypted SQL Server Backup

The restore process is as usual. You don’t have to specify any extra options, but the certificate or key used to encrypt the backup must be available on the instance.

USE master

RESTORE DATABASE [SampleDB] FROM DISK = N’C:\MyPath\SampleDB.bak’ WITH REPLACE;
GO

 

SQL Server Backup Encryption

To encrypt a backup we need to configure an encryption algorithm (supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES) and an encryptor (a certificate or asymmetric key). So if you don’t have a Database Master Key defined you need to create one. …