Backing up a SQL Server Database with Encryption

Backing up a SQL Server database with encryption is an essential practice when dealing with sensitive data, particularly if backups are stored off-site or include private customer information. SQL Server provides a secure method to back up databases using encryption by specifying an encryption certificate with the BACKUP DATABASE command.

Unlike older versions of SQL Server, where backups could be password-protected, starting with SQL Server 2012, backups require encryption to secure the backup file. The process of encrypting a database backup involves using an encryption certificate that ensures only authorized users can restore the backup. Below, I’ll walk you through the process of encrypting SQL Server backups.

The demo below guide was created by following MS Docs: Create an Encrypted Backup.

Backup a Database with Encryption

1. Create a Database Master Key:
Before you can create a certificate, ensure that a database master key exists in the database. If not, you’ll need to create one.

-- create master encryption key for sql
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere';

2. Create the Encryption Certificate:
The certificate will be used for the backup encryption process. It’s stored in the master database and is required for both creating and restoring encrypted backups.

-- create cert in mssql
USE [master];
GO
CREATE CERTIFICATE AT_Backup_Cert  
   WITH SUBJECT = 'Backup Encryption Certificate';
GO

3. View the Created Certificate:
After creating the certificate, you can verify it by querying the sys.certificates view. It’s important to monitor certificates for expiration dates and ensure they’re backed up regularly.

USE [master];
GO
   SELECT * 
   FROM sys.certificates
   WHERE name = 'AT_Backup_Cert'
GO

4. Backup the Database with Encryption:
Now that you have the certificate, you can use the BACKUP DATABASE command to encrypt the backup. Specify the ENCRYPTION option along with the encryption algorithm and the certificate to use.

-- backup database with encryption mssql
BACKUP DATABASE [Computer_World] TO DISK = N'C:\Temp_MSSQL_Backups\computer_world_full.bak'  
WITH COMPRESSION, ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = AT_Backup_Cert),STATS
GO

The database has successfully been backed up with encryption.

In this example:
– The backup is stored in the C:\Temp_MSSQL_Backups\ directory.
– Compression is enabled to save storage space.
– AES-256 encryption is applied using the AT_Backup_Cert certificate.
– The STATS option provides progress information during the backup.

5. Backup the Certificate and Private Key:
After backing up the database, SQL Server will issue a warning stating that the certificate used for encryption hasn’t been backed up yet. It’s critical to back up both the certificate and its associated private key to ensure the backup can be restored later.

The warning we see is regarding the certificate created prior to the backup is as follows:
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.

Hope this was useful. Next up, you should follow my post for How to Backup a SQL Server Certificate!