what server events I can use to "do something" on before restore of a database?
1
vote
0
answers
72
views
on this page: DDL Events you can see a complete list of the ddl events.
these events are server or database things that happen - like - create a database, alter a sequence, update statistics, create a table, create a trigger, create an availability group, etc,
the complete list you can find by running this query:
question is:
what in this list I can use to "do somethings" before I restore a database?
I can always do it manually but is there a way I could intercept the event of restore into a database, or restoring into a new database and take action before it happens?
Actually the trigger does not get fired
set transaction isolation level read uncommitted
set nocount off
drop table if exists #Radhe
;WITH DirectReports(name, parent_type, type, level, sort) AS
(
SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)
FROM sys.trigger_event_types
WHERE parent_type IS NULL
UNION ALL
SELECT CONVERT(varchar(255), REPLICATE ('| ' , level) + e.type_name),
e.parent_type, e.type, level + 1,
CONVERT (varchar(255), RTRIM(sort) + '| ' + e.type_name)
FROM sys.trigger_event_types AS e
INNER JOIN DirectReports AS d
ON e.parent_type = d.type
)
SELECT parent_type, type, name
into #Radhe
FROM DirectReports
ORDER BY sort
SELECT parent_type = 0
,type='execute at the database level'
,name='Events listed under DDL_DATABASE_LEVEL_EVENTS execute at the server (instance) or database level'
--,level=0
--,sort=0
UNION ALL
SELECT parent_type = 0
,type='execute at the server level'
,name='Events listed under DDL_SERVER_LEVEL_EVENTS execute only at the server level'
-- ,sort=0
UNION ALL
SELECT parent_type = 0
,type='------------------------------------------------'
,name='-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
-- ,sort=0
UNION ALL
SELECT parent_type, cast(type as varchar(150)), name
from #Radhe
partial view:

before
It works afterwards, it just throws an error so rolls the change back. There is no way to make a DDL trigger fire before the action was executed, you can only catch it immediately afterwards in the same transaction. - thanks for the clarification to Charlieface in the comments.
Asked by Marcello Miorelli
(17274 rep)
Nov 12, 2024, 12:10 PM
Last activity: Nov 19, 2024, 05:24 PM
Last activity: Nov 19, 2024, 05:24 PM