Is it possible to have a database trigger on restore in SQL 2005 or 2008

Question

I have some book keeping tasks (reset high water marks, clear some staged data) that need to be done after each restore of a QA database. I know that I can create triggers on databases in SQL but I do not seem to be able to find a way to do it on a database restore.

Since I work on a team of people with shared ownership of the database, I cannot always know when a restore occurs. Is there a way to automate this either with a trigger or some other database magic so that the cleanup procedure will always be called?

Answer 1

you can create triggers on a Database in SQL? i know you can create triggers on tables, but i've never seen one on a database.

you could try creating a job that runs every so often (say, once an hour) that looks at the status of the database (i don't know the exact command) and have the job do things depending on what you get in return.

Answer 2

http://www.eggheadcafe.com/conversation.aspx?messageid=33217985&threadid=33217985 http://msdn.microsoft.com/en-us/library/bb522542.aspx http://msdn.microsoft.com/en-us/library/ms189871.aspx

Doesn't appear possible. What about scripting the restore and including the maintenance procs at the end of the script?

Answer 3

There is no DDL trigger for RESTORE, there is however Event Notification for the AUDIT_BACKUP_RESTORE_EVENT event, which will get you where you want.