I have a SQL Server and I want to put some surveillance on it, to know if someone is taking a backup. I know that I can make an audit to monitoring the backup. But I want to have some kind of trigger every time someone takes a backup, so I can send an email to myself about it. Is it possible? (To get an email every time a backup is taken.)
Trigger cannot be fired on a backup event. Any kind of database backup cannot fire any trigger event. However you can write code to send email notofication to yourself or concerned person after backup completes successfully.
To actually create a survillience you create your own code which after taking backup calls SQl Server mail profile and sends notification to desired persons.
You can create a server level event notification for the Audit Backup/Restore Event Class event. In processing the event you can do whatever you wish, including sending yourself email. See Event Notifications and
CREATE EVENT NOTIFICATION. Event notifications can be used to trigger activated procedures.