Menu & Search

Backing up a SQL Server Database with Encryption

Backing up a SQL Server Database with Encryption

Backing up a SQL Server Database with Encryption is the secure way of performing your SQL Server backups. If you’re ever worried about the data within your databases, you may consider this option when performing database backups.

Interesting note; as from SQL Server 2012 it was not possible to create a backup with the PASSWORD/MEIAMPASSWORD options.

This guide was done following Microsoft Docs – Create an Encrypted Backup.


Backup Database with Encryption

Create a Database Master Key (is stored in the database).

Create a Certificate to use for the database backups (gets stored in the Master Database) – Microsoft Docs; Create Certificate

SQL Server Create Certificate

Have a look at the new certificate by querying sys.certificates (think about expiry dates).

SQL Server Select Sys.Certificates

Now backup the database.

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
SQL Server Backup Database With Encryption

The database has successfully been backed up with encryption.

The warning we see is regarding the certificate created prior to the backup.

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.

I have another post to guide through a certificate backup here.


Share

1 Comment

  1. […] for example, your SQL Server database backups are encrypted and you need to restore it on another server, then you will need both the certificate and private […]