Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.

share|improve this question
    
Are you controlling when the database is restored? –  Shawn Melton Sep 4 '13 at 13:10
    
This is a couple of lines with PowerShell. I'd probably choose that route over trying to completely contain this within SQL Server. With SMO, you can do a restore synchronously. When that is complete fire off an email using the System.Net.Mail namespace. –  Thomas Stringer Sep 4 '13 at 13:18
    
You can't configure Database Mail to do this directly, but you can create a server-level trigger that calls the Database Mail stored proc. I do this on our live servers to notify the admin team of any databases that are either created or dropped. I did this a good while ago, so the details are fuzzy -- I think the only way to differentiate between a new database and a restored database is text searching the event XML. –  Jon Seigel Sep 4 '13 at 13:24
2  
@Jon Seigel: There is no server level trigger for DB restore –  TToni Sep 4 '13 at 16:09
    
@TToni: I went back and looked at the code; it uses the CREATE_DATABASE event, so I tried it while restoring, and you're right, it doesn't fire either when a db is restored outright, or WITH REPLACE. I guess getting e-mailed when databases are created was enough for us. My bad. –  Jon Seigel Sep 4 '13 at 17:34

2 Answers 2

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 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.

share|improve this answer

The AUDIT_BACKUP_RESTORE_EVENT from Event Notifications can be used, but the only catch is that it fires on backup/restore start.

sys.dm_exec_requests DMV has a column command that can be queried for RESTORE DATABASE in order to watch for restore session to complete.

Another way is to periodically query

  • Default trace for EventClass = 115 AND TextData LIKE '%RESTORE%' as Aaron points out here.
  • msdb.dbo.restorehistory for new databases that are restored.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.