SQLTeam.com | Weblogs | Forums

Always backup database with encryption


is there a way to force that all backup database and backup log that runs on the server will be enforced with the encryption(if not specifying the encryption the query will be terminated )?


Nothing that's built in, I'm afraid. Take a look at Event Notifications:

There is an AUDIT_BACKUP_RESTORE_EVENT that you can capture, and then process through an activation procedure. You can parse the event text in that procedure and determine whether to cancel/kill the SPID doing the backup. Killing the backup session though is not ideal, but I don't know of another way to prevent it.

Setting up event notifications may be a bit off-putting, due to all the service broker stuff, but they are incredibly powerful and useful. Suggest you check out https://thebakingdba.blogspot.com/ , he has a lot of stuff on event notifications (might have to hunt for it on his blog though) although I'm not sure if they directly address your request.

Here's a video presentation he did on event notifications if you want to learn more: