Salesforce relational data backup and reload after sandbox refresh

Question

I have a specific situation. There is an important relational data set (data spanning across more than 5 tables) in full sandbox which I do not want it to be deleted when I do the sandbox refresh with Prod. Due to the volume of the data set, using a tool like data loader to backup the data before the refresh is not a viable option.

Are there any other options to save/backup the data set so that it can be pushed to the sandbox after the refresh?

Answer 1

If you do not have ETL tool and ordering a new fullcopy sandbox is expensive and not a viable option, then best way to do the data backup is to use Data loader will Bulk API option. I have seen that for 2 Million records it takes hardly a minute though it depends on number of fields which you are taking the backup.

Approach of Data migration and Data preparation

Since after sandbox refresh you need to migrate the same data maintaining the relationship, there should be an approach to do that otherwise maintaining relationship with newly created Ids will be an issue.

Here is the trick.

Let's say you want to take backup of Account, Contact, Opportunity, Task.

1. So, in every object; create an externalId field.
2. Take an extract of Account records and then in the excel, copy the Id field value into externalId field.
3. Then update Account records in Salesforce so that every records have externalId values populated. When you update the Account records, take only 2 fields, that is Id, and ExternalId.

4. Follow the same process for Contact. During taking extract of the Contact, take the AccountId for relationship.

5. This way, take extract of all the required objects.

Uploading the data upon maintaining the relationship

1. For the top most parent object, use Insert operation to upload the data. Let's say it is for Account.

2. When you upload the child data (Let's say, Contact or Opportunity), choose Upsert option in data loader. We know upsert operation is slower than insert and update. But when you select this option, data loader will guide you to select relationship based on External Id.

3. Select contact as object and choose file to upload and then click next. Here choose External_Id based on which contact record will get recognized.

4. In this screen, you need to select the External Id of Account based on which relationship will be maintained.

5. Rest of the steps are straight forward.

Answer 2

Please find my answer below but note some questions which would be helpful for SFSE to know:

• I assume the data set over the 5 sObjects isn't in Production already? If the full data set is already in Production, then as long as you don't exclude the 5 sObjects in a sandbox template, you won't need to go down this exercise unless you want an 'explicit' backup (.csv backup instead of in full copy Sandbox).
• Also, how large is the data set in question? You mention 'Due to the volume of the data set, using a tool like data loader to backup the data before the refresh is not a viable option' but Salesforce can handle even billions of records (some customers have hundreds of millions of records and even billions, especially for particular sObjects like Activites). The Data Loader, with Bulk API enabled, can manage up to 100 million records.

Possible Solutions:

Unfortunately, it seems that the Data Export function is not possible for sandboxes, as this would have been the most ideal answer. So you have a few options below, assuming that you don't have an ETL tool or External Objects with Salesforce Connect.

Going forward, you may want to consider Big Objects if the data set among the 5 sObjects is particularly large.

1) Use the Data Loader with the Bulk API enabled. This is covered by @Santanu Boral's answer.

A few things to note about this method: The max batch size is 10,000 records and you can have up to 10,000 batches per 24 hour period. Official Bulk API limits documentation is here. Also, because the Bulk API is asynchronous processing, the order in which the records will be processed isn't deterministic.

So, it's possible (if the 'happy path' is met) to extract 100 million records in a 24 hour period via the Bulk API. Is your data set greater than this number?

2) Use the Bulk Query.

The Bulk Query leverages cURL to export up to 15GB of data that you query (15 files, 1 GB each) locally. To find out if this 15GB limit is sufficient, you should know that almost every record in Salesforce, regardless of how many fields it has populated, will typically be 2kb in size. So do 2kb * the number of records in the data set you require (you can use the Query Plan Tool to find the cardinality) to see if 15GB is sufficient.

The official documentation for the Bulk Query is here. This solution is a bit more technical as you'll need command line knowledge.

3) Leverage the Bulk Query with auto number or PK Chunking.

The official documentation for this is here and here. This is an optimum method for fast data extraction from Force.com.