This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Always backup database with encryption
Question
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.
asked 2021-09-12 by Avi
Answer
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.
answered 2021-09-12 by Andy Mallon