You can script a database to be encrypted during backup by using the WITH ENCRYPTION clause in the BACKUP DATABASE T-SQL command. This process requires a Database Master Key and a Certificate to be set up first.

Prerequisites: Setting Up the Encryption Keys ?

Before you can run an encrypted backup, you must have an encryption key and a certificate within your SQL Server instance to protect the backup. This is a two-step process performed in the master database.

1. Create a Database Master Key This key is the root of the encryption hierarchy in a database. It's used to protect the certificates. You only need to create this once in the master database.

   USE master;
   GO
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Your-Complex-Password-Here';
   GO

2. Create a Certificate This certificate is what will actually be used to encrypt the backup file. It is, in turn, protected by the Database Master Key you just created.

   USE master;
   GO
   CREATE CERTIFICATE YourBackupCertificate
      WITH SUBJECT = 'A Certificate for Encrypting Backups';
   GO

The Encrypted Backup Script ?

Once the prerequisites are in place, you can add the WITH ENCRYPTION options to your standard backup script.

This script backs up your database, compresses it, and encrypts it using the AES 256 algorithm and the certificate you created.

BACKUP DATABASE [YourDatabase]
TO DISK = 'D:\Backups\YourDatabase_Encrypted.bak'
WITH
    COMPRESSION,
    ENCRYPTION (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = YourBackupCertificate
    ),
    STATS = 10;
GO

Critical: Back Up Your Keys! ❗

This is the most important step. If you lose the certificate and the master key, your encrypted backup will be completely unrestorable. You must back them up and store them in a secure location, separate from the database backup itself.

1. Back Up the Master Key:

   USE master;
   GO
   BACKUP MASTER KEY TO FILE = 'D:\Keys\master_key_backup'
       ENCRYPTION BY PASSWORD = 'Another-Complex-Password';
   GO

2. Back Up the Certificate:

   USE master;
   GO
   BACKUP CERTIFICATE YourBackupCertificate
   TO FILE = 'D:\Keys\YourBackupCertificate.cer'
   WITH PRIVATE KEY (
       FILE = 'D:\Keys\YourBackupCertificate_PrivateKey.pvk',
       ENCRYPTION BY PASSWORD = 'A-Third-Complex-Password'
   );
   GO

Store the resulting files and the passwords you used in a secure password manager or physical safe.

For a visual guide, this video explains how to encrypt database backups using certificates in SQL Server.