Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
That's suprisingly difficult.
SQL Agent Alerts were the first stop, but because a restore doesn't generate an "error message" there is no way to trigger it (I tried message levels 1 and 10, but no banana). It might be possible with a WMI query, but I'm not proficient in that.
Policies and extended events can create the necessary conditions but have no way to alert you.
Audits don't have the necessary conditions.
So, brute force then. The "restoring ..." message goes into the SQL Server log and into the Windows application event log with source
Working with that, you can attach a task to that event - unfortunately without having the option to filter based on the text which you need for the database name filter. So, configure the task to start a powershell script which then reads all the "MSSQLSERVER, 3402" events in , say, the last 2 minutes from the application log (hopefully you don't have mass restores all the time), checks for the correct db name and sends an email to you if it finds one with the wanted name.
As I said, brute force, but works.
Another way is to periodically query