Post reply

E-mail notification for creating new db through triggers

  • October 7, 2011 at 11:31 am

    #258648

    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

    #1391894

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 7, 2011 at 11:59 am

    #1391907

    can you please describe it briefly.. im not getting properly

  • October 7, 2011 at 1:09 pm

    #1391942

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 7, 2011 at 9:30 pm

    #1392026

    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