Sample Header Ad - 728x90

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:
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: enter image description here 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 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