3

Is there a way to force all database and log backups run on the server to be encrypted?

If encryption is not specified, the query will be terminated.

1
  • Please add which version and edition of SQL Server to your question's Tags.
    – J.D.
    Sep 12 at 13:05
8

Not in SQL Server

There is no option to force BACKUP DATABASE & BACKUP LOG to specify WITH ENCRYPTION... as part of backup commands in SQL Server.

But if you want data in backups encrypted...

If you want data in backups encrypted, you can enable Transparent Data Encryption (TDE) on your database. When a database is encrypted with TDE, the backups contain the encrypted data. From the docs:

Backup files for databases that have TDE enabled are also encrypted with the database encryption key. As a result, when you restore these backups, the certificate that protects the database encryption key must be available.

Rather than forcing every backup to specify encryption, you're simply shifting the encryption upstream so that data is already encrypted when the backups run.

One important side effect of enabling TDE is that if you are using compressed backups, enabling TDE will make your compressed backups stop compressing. If you are on SQL Server 2016 or newer, you can use TDE with compressed backups, albeit with some bugs to be aware of.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.