Erik Darling points out that figuring out when a database restoration occurs is much more difficult than you’d hope:
Astute SQL-ers may attempt to add a trigger to the
restorehistorytable over in msdb. It’s in the
dboschema, which might make you hopeful. We all know triggers in that pesky
sysschema don’t do a darn thing.
You guessed it, restores get tracked there. So there’s, like, something inside SQL telling it when a restore happens.
Guess what, though? A trigger on that table won’t fire. Not
FOR INSERT, not
AFTER INSERT, and not nothin’ in between.
Read on for more things that don’t work… Also check out the comments; I think Dave Mason has the best answer there.