Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) 
  + CHAR(92) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
    DatabaseName,
    StartTime, 
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)  
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.

The database_started Extended Event is fired after a database is restored.

Create an event session that captures the database_id field with a predicate that looks for %RESTORE% in the sql_text field (note: I believe this is sufficient -- you'll want to test for yourself).

I'm not familiar enough with Extended Events to tell you the best way of how to respond to the event. It would be nice if you could fire a stored procedure directly; I don't know if that's possible, though. You can certainly poll the event buffer, however, and that is preferable to scanning the default trace -- not only for performance reasons, but if the server is very busy and the polling interval is too long, you could end up missing events outright. With the Extended Events method, missing an event would be very unlikely.

As you can see by the list of DDL events, a DDL trigger can't be explicit enough for your requirement.

I'd recommend writing a PowerShell script to do the restore or attach and then execute the stored procedure immediately following. Mind you, your shop will have to be instructed to use this instead of a typical restore or attach.

Unfortunately I don't believe there's a built-in way to accomplish this.