Search code examples
c#sql-serversql-server-2008-r2sql-smo

How to subscribe to Database attach/detach events?


I am writing a C# application which works with databases and creates a new database on monthly basis.

The older databases can be detached and attached by a user through SQL Management studio or other ways.

I am trying to figure out how to listen to sql server events (IF they exist) and catch when database has been attached or detached.

When attach operation occurs my application has to check the database and update table schema to the latest version.

I am also learning the SQL SMO and was thinking this library would present me the event listeners, but i fail to find them.

I could, of course, run a thread or a timer which would check for new databases every couple seconds. I have tried that and running SQLServer.Databases.Refresh() updates the database list which i can then iterate and work with.

But is there a simpler way of achieving this? Maybe there is an event like OnDatabaseAttach somewhere?

EDIT

Thank you elsausmc for directing me into the right direction. I have done a little search on ServerEventSet and came across this page.

I did a quick test and it turns out, that ServerEvent.CreateDatabase does fire up when database has been created OR attached, however ServerEvent.DropDatabase does not fire on detach. Might be some other event perhaps. Thats for me to find out next. Thank you :)

Been googling with 10+ different phrases and never came up with something simple as smo server events. Silly me.


Solution

  • Perhaps there isn't Attach/Detach in SMO and it is covered under CREATE and DROP? ServerEventSet.CreateDatabase and ServerEventSet.DropDatabase and handle the event? Sorry if you already looked into this.