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
Have a look at the new certificate by querying sys.certificates (think about expiry dates).
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
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.