How to Destroy a Salesforce Instance in 1 minute.

In the past few months I've been working on a tool that restores all or part of a Salesforce sandbox from a backup. Naturally I needed to restore the same sandbox over and over to debug the tool.

Hum...sandbox refreshes can occur at most once per day (developer sandbox). This note is about how I refreshed my sandbox dozens of times per day.

Here's the script:

#!/bin/sh
java -jar sqlforce.jar <<!
connect production gsmithfarmer2@gmail.com mypassword
echo Clearing gmsmithfarmer2 production
DELETE FROM Case WHERE id <> null;
DELETE FROM Opportunity WHERE id <> null;
DELETE FROM Lead WHERE id <> null;
DELETE FROM Campaign WHERE id <> null;
DELETE FROM Account WHERE id <> null;
echo Finished clearing gsmithfarmer2 production
!

The script uses the free SQL/Force tool from www.capstorm .com to do a lot of damage to my salesforce instance in just a few lines of code. Dangerous - yes. Convenient -- yes.

The SQL/Force tools also includes jython integration -- with this you do lots of wacky things to Salesforce data in just few lines of code.

Here's an important bit of SOQL "SELECT * FROM Lead" trivia I bet you do not know...

Quick quiz: If you run the SOQL statement:

•  SELECT id, LastModifiedDate FROM Lead WHERE LastModifiedDate

will it ever return records whose LastModifiedDate is TODAY?

If your answer is "No" then you are wrong. In fact it is pretty simple to make the SOQL return lots of records from TODAY. Here's how.

1. Start the SOQL statement
2. Modified one or more Lead records that match the SOQL criteria BUT have not yet been returned by the query.
3. The query will return the modified records WITH LastModifiedDate value outside the range of the SOQL statement.

I discovered this "feature" working with a customer who has a highly active Lead table (upserts every couple of seconds). Once in a blue moon a lead record would be copied to SQL/Server even though it did not meet the criteria of the SOQL (the app was CopyStorm -- www.capstorm.com). Though it took a while to figure out the problem (since it appeared to be random and rare), reproducing it was easy once the root cause was understood.