Post reply

SQL Trigger to Ignore DB snapshot

  • July 13, 2019 at 6:05 am

    #3662635

    Dear All,

    I have a trigger on Create database command which logs into audit tables.

    I want filter out this trigger for Snapshot DB creations. Is it possible?.

    Basically no trigger while creating snapshot DB's, but only for actual DB's.

    Snapshot command:

    'Create Database.. As snapshot on'

    Thanks.

  • July 14, 2019 at 6:10 am

    #3662770

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • July 14, 2019 at 9:14 am

    #3662777

    you can't prevent the trigger from executing but you can control what it does by looking at the event data and filtering if the command is a create snapshot one

    within your trigger do something as follows

    DECLARE @CommandText nvarchar(2000);
    SELECT @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(2000)');
    if (@CommandText not like '%snapshot%of%')
    begin
      -- do your audit code here
    end

  • July 18, 2019 at 7:07 am

    #3664046

    Thanks Fred, I will work around this.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply