SQL DBA Blog

To backup a database in SQL Server with Encryption, run the BACKUP DATABASE command with the ENCRYPTION option added to the SQL statement, and specify the valid encryption certificate name.

Backing up a SQL Server Database with Encryption is the secure way of performing your SQL Server backups. If your MSSQL database backups are off-site and/or you have sensitive customer information stored on them, then you may consider encrypting your database backups.

Since SQL Server 2012, it has not been possible to create a SQL backup with a password (PASSWORD/MEIAMPASSWORD backup options).

The demo below guide was done following Microsoft Docs – Create an Encrypted Backup.

Backup Database with Encryption

Create a Database Master Key (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 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.

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.