patternsqlMinor
Configure Email to be sent on restoring a database
Viewed 0 times
emailsentrestoringdatabaseconfigure
Problem
Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.
Solution
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.
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
MSSQLSERVER and event id 3402.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.
Context
StackExchange Database Administrators Q#49234, answer score: 2
Revisions (0)
No revisions yet.