Sql-server – How to remove triggers from the restored database in SQL Server

restoresql servertrigger

I am using SQL Server 2008 Management Studio. I've got a .bak backup file from a client to resolve a specific issue.

Once I restore the database from the .bak file, I am unable to do any alter query or alter master key.

In my new database, all the triggers that were created at the client environment DB is attached to it. So getting the below error.

Msg 208, Level 16, State 1, Procedure dbtrg_DBEventsmodel, Line 16
Invalid object name 'DBMonitor.dbo.ddl_DBEvents'.

Is there any way to detach those triggers in the restored DB ?

Best Answer

You have a database trigger that logs DDL event to a table ddl_DBEvents in the database DBMonitor.

You can either delete or disable the trigger from SQL Server Management Studio in the Object Explorer under YourDatabase - Programmability - Database Triggers.

AdventureWorks Example

You could also disable or delete the trigger from code:

DISABLE TRIGGER dbtrg_DBEventsmodel ON DATABASE;
DROP TRIGGER dbtrg_DBEventsmodel ON DATABASE;

References:

DISABLE TRIGGER (Transact-SQL) - Books Online
DROP TRIGGER (Transact-SQL) - Books Online

Related Question