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
— 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