How can I *automatically* “restore” a sandbox environment with exactly the same data set?

Question

I would like to be able to automatically "restore" a sandbox (actually developer org) with the same set of data. The org will be used for in-house training purposes, and the idea is that at the end of every training, we could automatically reset the org to the original data.

Given that I want this automated, will it be necessary to use the SOAP or Bulk API to delete all the records and then reload them from a set of saved csv files? If I understand the documentation correctly, I would need to:

• Batch up the data into sets of 200 or less
• then, insert each sObject type in the correct order (child records first)

Are there any free automation tools for this?

Answer 1

It could be automated, but you would probably need to write code because it would be a multi-step process.

You would need to:

1. Get Salesforce to enable audit fields if you want to set the Created Date
2. Import all parent records, likely with a field for an external ID
3. Download the new Salesforce ids from step 2
4. Import child records and associate to the ids from step 3
5. Repeat steps 3 & 4 for each level of child object

I do not know of any free tools that automate all of that, but there may be paid tools that can handle that kind of batching.

Answer 2

You can automate all of the tasks you describe - retrieve, delete, load fresh - using the batch mode of the free Salesforce Data Loader. You'll need to write some simple batch file code to tie it together. (I'm assuming from your description that you don't require an actual refresh, nor do you require metadata to be reset.)

Look in the Data Loader user guide for the command line reference; you set up some SDL and XML files that direct it what to do. We've automated tasks like this many times using Data Loader.

The only change you may need to make to your metadata is that if you are loading master-detail relationships, you'll want to add External ID fields to the parent records, and then insert the children using an upsert operation so that you can refer to the parents by External ID.

Answer 3

I am also not aware of any free tools for this, but you should be able to get there with a bit of up front effort. I think the most difficult part would be establishing the baseline data and finding/testing the proper sequence to restore it.

You can use Salesforce DataLoader to extract the data into CSV files when it is ready and also restore it.

Once you have that, you can set up the files for DataLoader to run from command line - dataloadercliq can help with that. Then just a shell or batch file to start all the steps, kicked off either manually or by scheduler.

edit: DataLoader will take care of batching the records into sets for you. You will need to insert the parent records first so the Id is created and map the original record Id into an External Id field for the lookup by the child.

Take a look at Data Loader and relationships on developerforce (a short article that reiterates Mike's steps).

You didn't mention any, but if configuration changes will need to be restored, you can use the Force.com Migration tool for metadata and/or Selenium for declarative (disclaimer: I have not used either of these yet).