Moving Data from Production to Sandbox

I just spent the afternoon tearing my hair out over what really should have been a simple process. Gotta love a learning curve!!

I have a client that’s on the old NPSF template and I’m planning to upgrade them to the new template – but I wanted to try it out in a sandbox first so I didn’t have an “surprises”. I thought this would be a piece of cake. Here’s what I learned (oh, and no, I haven’t even gotten to installing the new packages yet!):

1. First, Setup new Sandbox: But wait… with the Non Profit license that Salesforce donates, you don’t get the option of creating a sandbox with data; you can create a developer sandbox, which creates your exact SF configuration, but without data. That’s helpful, but only sort of. I need to see what the effects of installing the new packages are on the DATA. So, I thought, no problem, I’ll just export the objects in use to .csv files and import them back into the sandbox…

1. Next challenge: I got the data out just fine, (I used Apex Data loader v14) but then I needed to figure out how to get the data back into the sandbox. After a bit of poking around online, I found that to do this you have to make a change to the Data Loader settings. Change server host to: https://test.salesforce.com, (it’s https://www.salesforce.com for production databases), then log into your sandbox database using your sandbox username and password + sequrity token (note this is not the same sequrity token you use for your production db, you’ll need to re-set it, (Setup | My Personal Information | Reset My Sequrity Token) before you do this.

1. More Hair Pulling: OK, now I can load data into my Sandbox, and I load up the Organizations/Accounts file. Everything goes in OK except the one record that’s the ‘Individual’ Account record that so many contacts are linked to. I get the following error – “Record Type: id value not valid for the users profile: 012700000009SZlAAM”. Hmmm… I’m System Administrator. I go to Setup | Manage Users | Profiles - Click on System Administrator. Scroll down to Record Type Settings; click on Edit in the Accounts section. Move Individual over from Available Record Types into Selected Record Types; click OK; try import on ‘Individual’ account into Orgs again – it works!

1. Next Headache: “insufficient access rights on cross-reference id”!! So. I guess I thought this process would be easy because I could just import the object data from the production instance and since the id’s were all nicely linked, it would be a simple process, but what I quickly realized is that when you import data for say, Organizations, Salesforce replaces the ID – and if you say, tried to import Contacts with the field ID (from production), you’ll get the “insufficient access rights on cross-reference ID” message. This all makes perfect sense now that I think about it – of course ID’s are auto-generated fields and would have to be re-created in a new db. Here’s a sketch of my process:

1. 1. Create LegacyID fields (text 35) in Organizations, Contacts, Households, Donations
   2. Prepare Organization import file: rename ID field as LegacyID
   3. Import Organization data
   4. Extract Organization data with ID, Name, LegacyID (I don’t know why I think I need name, I guess it’s just reassuring to have it in the midst of all those weird numbers)
   5. Prepare Household import file: rename ID field as LegacyID
   6. Import Household data
   7. Extract Household data with ID, Name, LegacyID
   8. Prepare Contacts import data file: rename ID field as LegacyID, replace ID’s for Organization(AccountID) and Household (ONW_Household?) with new ID’s from export files using VLookup
   9. Import Contacts data
   10. Extract Contacts data with ID, Last Name, LegacyID
   11. Prepare Donations import file: rename ID field as LegacyID, replace AccountID with new ID’s from export file using VLookup
   12. Import Donations data
   13. Extract Donations; ID, Name, LegacyID
   14. Prepare OpportunityContactRoles import file: delete ID field (no need for LegacyID); replace ContactID and OpportunityID with new values from export files using VLookup
   15. Import OpportunityContactRoles
   16. Prepare Connections import file; delete ID, replace ContactID’s and OrganizationID’s in the following fields with data from export files using VLookup (RELATED_CONTACT__C, CONTACT__C, RELATED_ORGANIZATION__C)

That should do it. Whew. A lot more work than I’d planned on. Time to do this once I’d understood the process? About 1 hour (small org <>

source : http://ceenotes.wordpress.com/2009/01/28/moving-data-from-production-to-sandbox/