Sql-server – SQL Server Sandbox

sql serversql-server-2008

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

Best Answer

Another idea would be to simply set up a nightly job that does a copy_only backup and restores it on the dev server (or on the same server, if you only have one, but that might not be a great idea). The nice thing about this is that the restore can go to any server (or multiple servers), and can be completely decoupled from any activity on the primary database.

On server 1:

BACKUP DATABASE db TO DISK = '\\someshare\file.bak' 
  WITH COPY_ONLY, INIT, COMPRESSION;

On server 2:

RESTORE DATABASE db_dev FROM DISK = '\\someshare\file.bak'
  WITH REPLACE, RECOVERY;

You may need to also add MOVE commands if the disk layout between the servers is different (or if you're putting the copy on the same server).

RESTORE DATABASE db_dev FROM DISK = '\\someshare\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\somepath\somefile.mdf',
  MOVE 'log_file_name'  TO 'E:\somepath\somefile.ldf';

If you're restoring on the same server, you shouldn't have any issues with users. If you restore to a different server, your users will exist but the server-level logins may not. There are scripts to fix that, and a new feature in SQL Server 2012 (Contained Databases) which eliminates the problem altogether.

Related Question