SQL Trigger to Ignore DB snapshot

• GonnaCatchIT

  SSCrazy

  Points: 2609

  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.

• Site Owners

  SSC Guru

  Points: 80385

  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.

• frederico_fonseca

  SSChampion

  Points: 14053

  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
  

• GonnaCatchIT

  SSCrazy

  Points: 2609

  July 18, 2019 at 7:07 am

  #3664046

  Thanks Fred, I will work around this.