Post-restore Process

If you are involved in a lot of development work with a lot of environments then you probably get frustrated when you are given a new environment and it isn't set up correctly.  For instance, the CM guy (or DBA) forgets to sync the sql logins and db users.  Or, someone forgets to enable service broker.  Or, you use the CLR (sloppily, I might add) and your restored db isn't TRUSTWORTHY?  In all honesty, it's tough to blame the DBA or CM Guy.  We devs love to change our requirements constantly and turn on every whiz bang new feature that Microsoft gives us.  This causes the DBAs fits when creating repeatable environment builds.  I once consulted at a place that had a 3 page document on miscellaneous "tasks" that had to be performed in a newly restored database before it could be handed over as a non-prod environment.  

That's just crazy.  And it's difficult to maintain a document like that because at any given moment you could have multiple build documents based on how many releases and new features you need to support (like Service Broker).  Or you may have customers at various releases that need different processes executed.  

The solution is simple.  

1. Create a PostRestore stored procedure.
2. Compile it in your db.  
3. Train your DBAs and CM guys to ALWAYS run this proc whenever a db is restored.  Any db.  

What are the benefits of doing this?  First of all, you can distill that 3 page restore manual into a simple stored proc call for them.  That's a VERY good example of DevOps.  Second, you have the ability to "version control" your db restore process.  If you decide you need to ENABLE_BROKER in the next release of your software you simply make that change to your PostRestore proc and check it in with that release's code.  Now your DBA/CM Guy doesn't even need to know this is a new "feature" you are enabling/deploying. 

It would be really great if Microsoft gave us a way to run this automatically whenever a db is restored.  I've tried server triggers and custom events and have not found a better way to do this, short of creating a SQL Agent job that runs every minute looking for a newly restored db and then running the command...and that seems insane.  Somehow this seems like it should be a part of a contained database.  

This is very much like contained databases.