• Making Local Copies of Your Salesforce Data
• How to Make Local Copies of Tables
• How SF_Mirror Works
• Viewing the Job History
• SF_Mirror Automatically Updates Local Copies
• Including Archived and Deleted Rows in the Local Copy
• Best Practices Incorporated into SF_Mirror

Making Local Copies of Your Salesforce Data

One common use of DBAmp is to make periodic copies of Salesforce.com data in a local SQL Server database. Using a combination of Microsoft SQL Server jobs scheduled by the SQL Server Agent and DBAmp, you can import data from Salesforce.com and make local mirrored table copies.

The local mirrored tables are all located in a single database that you create. On a schedule you setup, a job runs that backups the current local table mirrored table and renames the _Previous table to the correct object name.

By default, DBAmp does not download the values of Base64 fields but instead sets the value to NULL. This is done for performance reasons. If you require the actual values, , change the Include Binary Field Values setting to True in the Settings page of the DBAmp Configuration program.

How to Make Local Copies of Tables

Follow these steps to make a local copy of a table:

1. Create a job with one job step using the following: 

   Use “𝘚𝘢𝘭𝘦𝘴𝘧𝘰𝘳𝘤𝘦_𝘣𝘢𝘤𝘬𝘶𝘱” EXEC SF_Mirror ‘𝘚𝘈𝘓𝘌𝘚𝘍𝘖𝘙𝘊𝘌_𝘓𝘚’, ‘𝘈𝘤𝘤𝘰𝘶𝘯𝘵’

   where SALESFORCE_LS is your linked server name, Account is the object name, and Salesforce_backup is the database name.

2. Open the right-click menu and select Start Job.

3. (optional) You can schedule a SQL Server job to run SF_Mirror by modifying the job schedule for your execution schedule.

How SF_Mirror Works

SF_Mirror automatically chooses whether to do a full copy or a delta copy of the table. It decides this based on these criteria:

• SF_Mirror creates a local table with the contents of the same object at Salesforce.com if the table does not already exist locally.

• If the table exists locally, SF_Mirror decides whether to do a full copy or a delta copy of the table. It makes this decision based on the created date of the local table (the last time the table was replicated).

  • If the created date of the local table is more than seven days old, it makes a full copy of the table.

  • If the created date of the local table is less than seven days old, it creates a delta copy of the table.

  • If there are any schema changes detected, it makes a full copy of the table.

The name of the local table is the same name as the Salesforce.com object (e.g., “Account”). By default, SF_Mirror uses the BulkAPI (with the PKChunk header where applicable) when making a full copy of the table locally.

NOTE: If the table has been mirrored locally, when doing a full copy SF_Mirror uses the SOAP API if the row count of the local table is below 20,000, and the BulkAPI if the row count is above 20,000. In addition, SF_Mirror creates a primary key on the ID field of the local table and preserves any secondary indexes on the local table.

Viewing the Job History

The output from the DBAmp stored procedures can be long and is often truncated in the normal job history. For this reason, you should modify the job step to retain the job output in a table or file.

To retain the entire step output, edit the job step and navigate to the Advanced tab. Select Route to table to have SQL Server retain the entire message output in a table.

To view the output, return to the Advanced tab and select View.

SF_Mirror Automatically Updates Local Copies

After SF_Mirror has created an initial set of local, mirrored tables, subsequent calls automatically keep those tables up to date by downloading inserted, updated, and deleted rows since the last run.

Including Archived and Deleted Rows in the Local Copy

SF_Mirror does not include archived and deleted records of the Salesforce.comb object when making a copy of Salesforce data by default. To include the archived and deleted records of the Salesforce.com object in the copy of Salesforce data, add the queryall switch. For example:

 EXEC SF_Mirror 'Salesforce', 'Account', 'queryall'

SF_Mirror retains the permanently deleted rows from run to run. Once you begin to use queryall option for a table, all future SF_Mirror calls for that table must use queryall. Otherwise, you lose all permanently deleted rows in the local table.

Best Practices Incorporated into SF_Mirror

We recommend that you run SF_Mirror hourly, nightly or weekly. In the Salesforce API, changes in formula fields are not flagged as changed records. Therefore, if you have formula fields on objects and only their value changes, SF_Mirror does not update the record when doing a delta copy. This is because the Salesforce API does not update SystemModstamp field of that record for a formula field change. SF_Mirror automatically performs a full copy every seven days to update with these formula field changes.