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 checkout my post: How to Backup a SQL Server Certificate!

Backing up SQL Server certificates is essential if you use them, especially for encrypted database backups that may need restoration on another server.

This guide explains how to securely back up a SQL Server certificate and its private key.

Backing up a SQL Server Certificate

1. Check Existing Certificates

To check your existing SQL Server certificates you want to back up, run the query below. We’re querying sys.certificates to get this info.

-- get server certificates
Use Master  
GO  
	SELECT name, pvt_key_encryption_type_desc, subject, start_date, expiry_date, key_length
	FROM sys.certificates
GO

2. Backup the Certificate and Private Key

Replace file paths and passwords as needed in the SQL command below. Check out the MS Docs for Backup Options and more info.

-- backup sql server certificate
BACKUP CERTIFICATE AT_Backup_Cert TO FILE ='C:\Temp_MSSQL_Backups\mssql_at_backup_cert.cer'  
      WITH PRIVATE KEY (   
		FILE ='C:\Temp_MSSQL_Backups\mssql_at_backup_key.pvk',  
		ENCRYPTION BY PASSWORD ='Str0ng3P4sSw0rd!' ) 

3. Secure the Backup Files

Move the .cer and .pvk files to a safe location, such as an encrypted storage device or secure cloud storage.

Notes

Use strong passwords to encrypt your SQL Server certificate backups.

Certificates are needed for restoring encrypted backups, and losing them can result in permanent data loss if caught in a Disaster Recovery incident.

Store backups securely and restrict access to authorised personnel only.