October 7, 2011 at 11:31 am
Hi Friends,
Is there any way to get email notifications from triggers if any new db is created in the sql server?
Thanks in advance.
October 7, 2011 at 11:36 am
shilpa.shankar87 (10/7/2011)
Hi Friends,Is there any way to get email notifications from triggers if any new db is created in the sql server?
Thanks in advance.
you would have to use Extended Events and watch for CREATE_DATABASE and the AUDIT_BACKUP_RESTORE_EVENT DDL events. from that process, you could send yourself an email.
you need the AUDIT_BACKUP_RESTORE_EVENT DDL event becasue you can create a new database from a restore, and that doesn't count as a CREATE_DATABASE DDL event.
Lowell
October 7, 2011 at 11:59 am
can you please describe it briefly.. im not getting properly
October 7, 2011 at 1:09 pm
one of the more advanced features in SQL Server is the ability to use Extended Events.
those events allow you to capture certain events and respond to them asynchronously, like logging and auditing some info about the event, or sending an email.
Extended events are not like triggers, where you can stop certain things by rolling back the transaction, but are a nice way to notify yourself.
The link i posted from Books Online has a better explanaition than i can do just to in the forum here.
another way is to have a job compare the list of current databases against a known list every x minutes.
if a database in master.sys.databases doesn't appear in your SavedDatabases table (that you created previously), that job could send an email notification as well.
that process would not catchg when a database was created and dropped between the x interval you select, but it's probably rare that that ever occurs anyway.
Lowell
October 7, 2011 at 9:30 pm
Extended events is probably the feature you should use for this, but you could also write a server-scoped DDL trigger to catch this as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply