We have a staging database in which tables get dropped and recreated every day. I have set it to Simple recovery model numerous times, but it keeps getting changed back to Full recovery (I suspect a maintenance script somewhere, possibly even running on a different server). The log does not get backed up, so eventually it fills up and crashes whatever is loading data into the database.
I would like to create a DDL trigger that will set recovery mode back to Simple when it gets changed to another model. I've read the MSDN site, but not sure if there is an event that fires and how I would code. I want to only monitor this one database.
We are running SQL Server 2012. Do DDL triggers have to be 'enabled' or is the mere creation of one all that is necessary?