9

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

|improve this question
8

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.

|improve this answer
  • We have dev/prod but dev is the only server in which this would be happening. Prod is only for prod-ready processes. – Kittoes0124 Mar 22 '13 at 17:01
  • This is the solution which I would choose, just please have in mind that in most cases you don't want to simply copy production to dev environment. Please have a measure (script?) in place, which will, for example, remove or obscure users email addresses, contact details, etc. You don't want your devs to accidentally start sending emails to users. – zeroDivisible Apr 30 '13 at 8:59
5

Since you have an instance with the Enterprise Edition engine, I would use database snapshots.

This will allow you to quickly and easily roll back any changes made during the day, without having to restore the entire database.

Note that if the developers are planning to do big data loads (sounds like they aren't?), then this may not be appropriate.

|improve this answer
  • Why wouldn't it be appropriate if they were doing big data loads? Ours might load say.... 8 million rows of 100 columns every now and then (even if they "shouldn't" be) but we don't necessarily want to prevent them from doing so. All we really care about is that everything gets nuked at the end of the day. – Kittoes0124 Mar 22 '13 at 16:58
  • 2
    @Kittoes because a snapshot has to be maintained when source data changes. It needs to pull existing pages from the source if the source changes, so that it maintains the "before" view. It doesn't do that until the source data changes (a snapshot uses a sparse file that is empty except for deltas). This maintenance can become quite expensive. See how database snapshots work. – Aaron Bertrand♦ Mar 22 '13 at 17:03
  • @AaronBertrand Ok, so if the database grows to 8GB during a day then when the snapshot is restored all new data would be removed but the database would still be 8GB in size? Or am I misunderstanding? – Kittoes0124 Mar 22 '13 at 17:10
  • @Kittoes a snapshot is read-only, so you would only be able to load new data into the source database. If you add 8GB during the day, it will not be visible to the snapshot. When you revert / drop the snapshot, the source database will still have that 8GB of data and will be sized accordingly. If you then take another snapshot, the new data will be visible. If you remove 8GB during the day, it will be added to the snapshot. – Aaron Bertrand♦ Mar 22 '13 at 17:25
  • 1
    @Kittoes if you mean you want to undo the 8GB data load by reverting to the point in time that the snapshot was taken, yes it should return your data files to the size they were (whether you really want to have the files small again so you can just autogrow more when you load 8GB again tomorrow is another issue). But I haven't tested that scenario explicitly. And as the article I linked to mentions, this is not necessarily foolproof, as it also hinges on the reliability of the underlying storage. A backup is a safer way to do that. – Aaron Bertrand♦ Mar 22 '13 at 17:39
0

Let me add my few cents to see if it helps you :

In my company we are having the same situation that every night the developers want to refresh the databases that they have been using through out the day. This means that we have a set of databases that Dev's do not touch -- lets say A and another set of databases which are exact copy A but they do their stuff but want to get refreshed every night -- lets say B . This happens on 1 single server instance.

What I have implemented is a NIGHTLY RESTORE PROCESS to achieve this. Below is how it works :

Create a driver table with list of databases that needs to be restored every evening (as you have mentioned).

Table: nightly_restore (OriginalDB, RestoreDB, backuplocation, enabled_YN, Results, PASS_FAIL)

Then you can write some TSQL that will loop through the list of databases from above table and then perform the restores and log any success or failures in Results and a bit 1=Pass or 0 = Fail. Enabled_YN will determine if that database needs to be restored or not.

If there more databases that will be added in future then you have to just insert those in the table and set the enabled_YN bit to Y (enabled).

This way the process will be more flexible and manageable.

If you want the SQL that I have written (I am sure, you will be able to write it :-)), just ping me or add a comment and I will share it.

HTH

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.