I'm attempting to set up a sandbox for our report developers to their work in. My current plan is to "reset" the database every evening but I'm not sure how to go about doing so. What I mean by reset is that I want to essentially drop any user tables, views, stored procedures, etc from all but one database on the server. I suppose another option would be to drop and recreate the database as well but I'm pretty sure that'd mean regranting access to all of the appropriate AD groups/people too.
I really don't know what would be the best way to go about doing this so I'm hoping some of you will be able to provide some good ideas/suggestions. Thanks.
For clarity, we essentially want to do this with our database: http://try.discourse.org/t/this-site-is-a-sandbox-it-is-reset-every-day/57. Only difference being is that we don't want to recreate our users every day.
Version: SQL Server 2008
Edition: Developer & Enterprise