Prior to running CopyStorm for the first time, create a fresh SQL/Server database.

SQL/Server Connection String

The only difficult part when copying Salesforce to SQL/Server is constructing the connection string. Clicking on the Connection String Builder will open a form that, if you are running SQL/Server using a standard configuration, will construct a connection string for you.

The connection string can be verified by pressing the “Test SQLServer Connection” button.

The general form of a SQL/Server connection string is:

• [server-name[\instance-name][:port-number]][;property=value]*

If you would like to see more information regarding SQL/Server connection strings or are not familiar with Microsoft’s type 4 JDBC driver, these resources may help:

• https://docs.microsoft.com/en-us/previous-versions/sql/legacy/ms378428(v=sql.90)

• http://www.connectionstrings.com/

Capstorm includes a MySQL driver from the MariaDB project to communicate with both Oracle’s MySQL product and with MariaDB.

Since MariaDB is provided by the original authors of MySQL and strives to maintain compatibility with Oracle’s MySQL, the driver packaged with CopyStorm is probably the only driver you will need. However, if you are encountering compatibility issues or other problems with MariaDB’s MySQL driver, you can download and install Oracle’s MySQL driver by hand.

CopyStorm does not package this driver with the product due to Oracle’s licensing policy for ISV’s, which makes it difficult to package the Oracle MySQL JDBC driver with a product.

How to Install Oracle’s MySQL JDBC Driver (not recommended for most cases)

To install the Oracle MySQL JDBC driver:

• Delete the file lib/mariadb-java-client-*.jar from the CopyStorm installation directory.

• Restart CopyStorm.

• Choose MySQL as the database type.

• CopyStorm will ask you if it’s OK to download and install Oracle’s JDBC driver.

If you would prefer to install Oracle’s JDBC driver by hand:

• Go to Oracle’s download page

  • http://dev.mysql.com/downloads/connector/j

• Download and unzip the driver.

• Copy the jar file to CopyStorm’s lib directory.

• Restart CopyStorm

Common Problem – TCP/IP is not enabled

All MySQL JDBC drivers communicate with MySQL using TCP/IP. If CopyStorm is having trouble connecting to the MySQL database, you may not have TCP/IP enabled. If so, there are a number of resources providing instructions on enabling this feature (the manual for your version of MySQL, running a web search for “MySQL enable TCP”, etc…).

MySQL Connection String

The only complicated configuration required in the CopyStorm Main tab is the MySQL connection string. Clicking on the Connection String Builder button will open a form that will construct the connection string for you, given database information.

The connection string can be verified by pressing the “Test MySQL Connection” button.

The format of a MySQL connection string is:

• //[host][:port]/[database-name]

Example:

• //localhost/MyCopyStormDatabase

Initial Step

Prior to running CopyStorm for the first time, create a fresh Oracle database.

Oracle Connection String

The only complicated configuration required in the CopyStorm Main tab is the Oracle connection string. Clicking on the Connection String Builder button will open a form that will construct the connection string for you, given database information.

You will likely need one of these forms:

• myDomain.com:1521:orcl

• myDomain.com:1521:xe

• localhost:1521

The connection string can be verified by pressing the “Test Oracle Connection” button.

The following is a screen shot of a working Oracle setup on one of our test computers:

Initial Steps

Prior to running CopyStorm for the first time, create a fresh PostgreSQL database.

Connection String

The only complicated configuration required in the CopyStorm Main tab is the PostgreSQL connection string. Clicking on the Connection String Builder button will open a form that will construct the connection string for you, given database information.

The general form of a PostgreSQL connection string is:

• [//host[:port]][/database-name]

Example:

• //localhost/CopystormBackupDatabase

By default, a PostgreSQL connection will use the public schema. This can be overridden by entering a schema name.

The following is a screen shot of a working PostgreSQL setup on one of our test computers:

The Configuration Tab contains options to modify the CopyStorm runtime, including the list of Salesforce Objects/Tables to copy.

The default view of the Configuration Tab contains core configuration options that are commonly modified.

To view additional options, change the “Configuration Level” dropdown:

• Standard

  • Core CopyStorm configuration and performance tuning.

  • Options used to ensure that CopyStorm does not interfere with other operations (e.g. limiting Salesforce API Call consumption).

• Advanced

  • CopyStorm troubleshooting and logging options.

• Developer

  • CopyStorm application tuning options, advanced network configurations, and query optimization options.

The Guardrails Tab is used to limit the potential impact of individual CopyStorm runtimes and to ensure that CopyStorm jobs do not interfere with other operations.

The Database Reset Configuration Editor is used to delete and rebuild portions of the CopyStorm database.

Options within the Database Reset Configuration Editor are only used in specific circumstances and should not be left enabled as part of normal backups.

The External Configuration Editor is used to provide external folders used by CopyStorm to read configurations and to store temporary files.

The Database Indexing Configuration Editor is used to enable or disable automatic database index creation.

Though every Snapshot Policy has a default record retention rule, it is often convenient to have a custom retention rule for specific tables. Example:

• By default, retain records in a snapshot for 90 days.

• Retain Account, Contact, and Opportunity records for 365 days.

• Retain Case records for 2 years.

• Do not retain EmailMessage records.

The following two screens illustrate how to set up this policy.

Clicking on the Advanced button launches an editor to override the default record retention rule. The following example sets up the scenario described at the top of this page.

The All Changes Filter Rul e always indicates that a record should be snapshot for a change to any field. Basically, if a caller asks if a record should be snapshot then the All Changes Filter Rule will always respond with “Yes.”

This rule is useful for any table where all changes should be included in the snapshot.

Since the All Changes Filter Rule always says “Yes, this record should be snapshot”, naturally its only configuration parameter is which tables to include in the snapshot.

A SQL Predicate Filter Rul e is useful when other filtering techniques are insufficient and it is possible to write a database SQL fragment to identify records that match a snapshot policy. It is rare to need this filter rule because filtering can typically be accomplished using the graphical Column Expression Filter Rule.

A SQL Predicate Filter Rule has a single configuration parameter: a fragment of database specific SQL will be added to the WHERE clause of a SQL statement generated by CopyStorm. Example:

• SELECT Id FROM Account WHERE Id IN (…) AND (configuration parameter from SQL Predicate Filter)

If the argument to a SQL Predicate Filter Rule was “RecordAge__c>46 OR alwaysSnapshot__c=1” then SQL statements generated by CopyStorm would look like:

• SELECT Id FROM Account WHERE Id IN (…) AND (RecordAge__c>46 OR alwaysSnapshot__c=1)

A SQL Predicate Filter Rule also includes a rule for which tables to include in the snapshot.

Though it is possible to delete a Snapshot Policy, usually a site will simply want to make the policy inactive. When a Snapshot Policy is deleted both the policy definition and all data snapshot by the policy are deleted from the CopyStorm database.

The Snapshot policy deletion tool is launched using right-click context menu on the CopyStorm Snapshot tab.

The Run Snapshot Policy Retention Rules feature is used to force a snapshot to look for records that should be deleted from the Snapshot based on its retention rules and then delete the records. This happens automatically for active Snapshot Policies as part of the regular CopyStorm backup process, so this feature is most useful for inactive Snapshot Policies.

The Run Snapshot Policy Retention Rules feature is accessed from the right-click context menu on the CopyStorm Snapshot tab.

There are only two parameter options when running retention rules.

• Search Batch Size — number of records that will be searched at once for potential deletion.

• Deletion Batch Size — number of records that will be deleted in a single database transaction.

Like most database systems, the CopyStorm Snapshot system will operate most efficiently when its has statistics about what is stored in snapshots. Example:

• If CopyStorm knows what tables have been snapshot by a policy then it can limit searches to just these tables.

• If CopyStorm knows how may records are in a snapshot it can generated more efficient SQL queries.

For the most part, CopyStorm Snapshot updates statistics without any user intervention. This page, however, describes how to force CopyStorm Snapshot to update statistics if the most accurate statistics possible are desired or if the statistics have been corrupted. Note: it is unlikely that you will ever need this feature.

The Update Statistic s feature is accessed from the right-click context menu on the CopyStorm Snapshot tab.

In the Refresh Statistics dialog there is the option to refresh statistics for a single snapshot or for all snapshots in the system. The following image illustrates what the screen may look like after refreshing statistics for a single snapshot.

CopyStorm supports saving a backup configuration to a file in two formats:

• Plain-text XML.

• Encrypted text (256-bit DES encryption).

Both formats support the optional saving of Salesforce and database credentials. A CopyStorm backup configuration can be completely restored by going to File/Open in the application menu. To save a configuration, go to File/Save in the application menu:

The plain-text XML version of the file is fairly complex and not intended to be edited by hand, but it is possible. However, Capstorm may not provide support for problems arising from a hand-edited configuration file.

In certain configurations it may be necessary to run specific SQL statements or set database specific options each time a connection to a database is established.

Oracle

The configuration file “Oracle.xml” contains parameters that control connections made to Oracle. The following configuration directives are supported:

• InitSQL — specify a line of SQL that will be run each time a new connection to Oracle is established. There is no limit to the number of InitSQL elements.

The following Oracle.xml file will cause CopyStorm to run the command:

• alter session set ‘_optimizer_rownum_pred_based_fkr’=FALSE

Oracle.xml:

<Oracle>
    <InitSQL sql="alter session set &quot;_optimizer_rownum_pred_based_fkr&quot;=FALSE" />
</Oracle>

If your site does not permit communication with the default Capstorm license server located at https://license.capstorm.com, the configuration file “Capstorm.xml” allows overriding the endpoint.

An example that changes CopyStorm to use a http (non-SSL) endpoint for license management is:

<Capstorm>
    <licenseServer url="https://license.capstorm.com/LicenseStore/GetLicense" />
</Capstorm>

There are a number of other elements that can appear in a Capstorm.xml file, but you should not change without being instructed to by Capstorm. However, if you want to confuse your colleagues you can change CopyStorm’s background color:

<Capstorm>
    <backgroundColor red="255" green="100" blue="15" />
</Capstorm>

How to Solve OPERATION_TOO_LARGE Errors

If CopyStorm is reporting an OPERATION_TOO_LARGE error, it will be included in the CopyStorm log. The error will look like this:

09:02:11: [UnexpectedErrorFault [ApiFault exceptionCode=’OPERATION_TOO_LARGE’ exceptionMessage=’exceeded 20000 distinct ids’ extendedErrorDetails='{[0]}’ ] ] Before diving into the root cause, this is the most common solution:

• Grant the ViewAllData privilege to the Salesforce credentials being used with CopyStorm.

Root Cause

The root cause of this exception can be found in the Salesforce description of the error:

• OPERATION_TOO_LARGE

The query has returned too many results. If certain queries are run by a user without the “View All Data” permission and many records are returned, the queries require sharing rule checking. For example, consider queries that are run on objects, such as Task, that use a polymorphic foreign key. These queries return this exception because the operation requires too many resources. To correct, add filters to the query to narrow the scope, or use filters such as date ranges to break the query up into a series of smaller queries.

You can find the Salesforce documentation in the Core Data Types section.

Almost all CopyStorm sites will want to use Salesforce credentials with the ViewAllData permission because without this permission all data will not be backed up. In particular, no metadata will be backed up because the API call used to fetch metadata requires the ViewAllData permission.

I Need a Timebox Approach Because ViewAllData is Not Allowed

If your organization does not want to give your CopyStorm backup user the ViewAllData permission and an incomplete backup is OK, you can configure CopyStorm to backup data using a sliding timebox.

For example, to backup the Note table using a 60 day sliding timebox, the following TableRule must be added to your site’s TableRuleRegistry.xml file:

This XML rule means:

• Use a sliding 60 day window when backing up Note.

• Start the first search on 1-Jan-2001.

• Start subsequent searches at the timestamp on the most recent Note record in the backup.

The system will backup Note records in roughly the following order:

1-Jan-2001 thru 28-Feb-2001 1-Mar-2001 thru 30-Apr-2001 1-May-2001 thru 30-Jun-2001 etc… until the current date is reached.

There are a number of XML configuration files that can be used to override default CopyStorm behavior. This article describes the approach CopyStorm uses to find configuration files.

When CopyStorm searches for a configuration file, it looks in four places:

1. Configuration files embedded within the CopyStorm application.

   1. CopyStorm always looks here first.

2. Configuration files in the “config” subdirectory of the CopyStorm installation.

   1. CopyStorm always looks here.

3. Configuration files in the directory specified by the Configuration tab “Config Directory” option within the “External Configuration” section.

4. Configuration files in the directory specified by the “-config” command line argument.

   1. CopyStorm looks here ONLY IF the Advanced tab setting is blank.

If a configuration file is found in multiple locations, all are loaded in the order listed above. Later loaded files will override any configuration from earlier files.

Documentation on available XML configuration files can be found on this page.

Best Practices

Capstorm recommends the following best practices:

• Keep each CopyStorm configuration file in its own directory OUTSIDE of the CopyStorm installation directory.

  • This will make the CopyStorm upgrade process simpler.

• If you need custom XML configuration files then:

  • Store them in the same directory as the corresponding .copyStorm configuration file.

  • Set the Configuration tab “Config Directory” parameter in your .copyStorm file to point to the directory where the .copyStorm file is located.

Oracle 12c added support for extending the allowed length of VARCHAR2 columns from 4000 to 32,767 bytes but made the discovery of whether this feature is enabled require privileges that a normal Oracle user is not granted. This article explains how to tell CopyStorm that the Oracle feature “max_string_size=EXTENDED” is enabled without requiring special Oracle permissions.

For your reference, if an Oracle user has enough privileges then a simple query can be used to determine if the EXTENDED Varchar2 feature is enabled.

• SELECT name,value FROM V$PARAMETER WHERE name=’max_string_size’

It is likely that your account will return a “Table or View not Found” when running this query because of your limited permissions.

To inform CopyStorm that the EXTENDED feature is enabled simply create a file named Oracle.xml and drop into the your private CopyStorm configuration directory specified on the Advanced tab or into the directory [CopyStormRootDir]/config. The content of the Oracle.xml file should be:

<Oracle>

<DatabaseProperty name="max_string_size" value="EXTENDED" />

</Oracle>

Restart CopyStorm and future VARCHAR2 columns will be up to 32,764 bytes in length. You may wonder why the limit is 32,764 rather than 32,767. The reason is that Salesforce defaults to a UTF-8 Extended character set and each character can take up to 4 bytes.

Internally, CopyStorm replicates data to Snowflake by creating a CSV file of the records being backed up and uploading the CSV file to Snowflake. It may be required to change this staging location to meet business requirements. CopyStorm allows customers to specify a different Snowflake stage by following this process:

Create a xml file named “SnowflakeDatabaseBuilder.xml”

Then place the contents below into the xml file.

<SnowflakeDatabaseBuilder>
    <SnowflakeStage value="@clientProvidedStagePath"/>
</SnowflakeDatabaseBuilder>

You will need to replace “clientProvidedStagePath” with the Snowflake stage’s path, then place this file into CopyStorm/config directory and relaunch CopyStorm.

Note: This feature is available in CopyStorm versions 10.56.5 and above.

Background

Note: This feature is available in Capstorm application versions 10.56.6 and above.

Capstorm applications allows you to add supplementary Java options. These options serve to dynamically alter the operational parameters of the application prior to its initiation. One frequent utilization, which serves as an example throughout this article, is the facilitation of user-driven control over memory allocation for any of Capstorm’s applications. This capability gives users the control to finely calibrate the memory resources dedicated to the operation of Capstorm’s applications.

Configuration File Creation and Placement

Note: Application must be restarted for these configuration files to be applied.

Step 1 – File Creation and initial template setup:

1. First create a XML file named “AppLoader.xml”

Step 2 – File Placement:

Two recommended placement options:

• Option 1: If you followed CopyStorm’s Best Practice Directory Structure then we recommend placing this configuration file in CapstormConfig directory.

  • copystorm-directory-structure

• Option 2: Inside of one of Capstorm’s application installation directory there should be a directory called “config”. You can also place this configuration file in the “config” directory.

How to Customize Application Memory

Note: For versions older than 10.56.6 please visit: out-of-memory-java-heap-space-error on how to configure memory settings.

• Once you have completed “Configuration File Creation and Placement” steps you can simply copy the snippet below into the AppLoader.xml configuration file.

• The example below shows how to set the min and max memory allocations to 300 and 1000 megabytes, respectively.

How to Add Custom Java Properties

The AppLoader also allows for custom Java Properties. Below is an example of setting the HTTPS Protocols to TLSv1.1 and TLSv1.2.

While commonly used schema options within CopyStorm are directly exposed in the CopyStorm GUI, a number of schema initialization parameters can only be provided by providing CopyStorm with a JDBCDatabaseBuilderConfig.xml file.

To use the options detailed in this page, create a JDBCDatabaseBuilderConfig.xml file and copy it to one of the CopyStorm Configuration File Directories.

Capitalize Column & Table Names

The following XML will cause CopyStorm to create a Salesforce data table and column names in ALL CAPS.

<JDBCDatabaseBuilder>
<!-- 
    Set these values to "true" to capitalize table and column names.
-->
	<CapitalizeTableNames enabled="true" />
	<CapitalizeColumnNames enabled="true" />

</JDBCDatabaseBuilder>

Remove Table & Column Postfixes

The following XML will cause CopyStorm to remove Salesforce-standard postfixes from database table and column names.

The CopyStorm/Medic Log Manager tool supports the viewing, management, and exporting of the results of previously run CopyStorm jobs.

Starting with version 7.*.*, CopyStorm stores the status of each job into tables in the CopyStorm target database. The CopyStorm/Medic Log Manager is a convenient tool for viewing, managing, and exporting the contents of these tables. In addition to Log Manager, regular RDBMS tools can be used to access CopyStorm’s logs.

Full documentation for the CopyStorm/Medic Log Manager tool can be found here.

The database tables used for RDBMS access to CopyStorm and CopyStorm/Medic logs can be found here.

The CopyStorm/Medic Delete Salesforce Records tool has the ability to run batch mode without the need of opening the application GUI and click through the steps of the tool. Execution of the tool in this manner requires a CopyStorm/Medic configuration file that already has all desired configuration options and filters selected and saved. For more information regarding the setup of this tool, see here.

Example Usage

This example is for Windows installations of CopyStorm/Medic. For Linux/Mac installations, change CopyStormMedic.bat to CopyStormMedic.sh

CopyStormMedic.bat -tool deleteTool -run "/path/to/config.copyStormMedic"

When replicating Salesforce data and schema to the CopyStorm database, CopyStorm will create database tables and columns which mirror the object and field structure in Salesforce. For example, there is a Salesforce object called “Account”, with a String field called “Name”. This means that the CopyStorm database will have a table called “Account” with a VARCHAR or CLOB field called “Name”.

CopyStorm partitions use a technique called Vertical Partitioning to change the CopyStorm database schema by separating the database table for an object into multiple tables. Of course – CopyStorm, CopyStorm/Restore, and CopyStorm/Medic are fully compatible with CopyStorm partitions.

For example, by default, the CopyStorm database may have an “Account” table with these columns:

1. Id

2. SystemModstamp

3. Name

4. BillingStreet

5. BillingCity

6. BillingState

If the “Billing” fields are moved to a new partition, the database will have multiple tables:

1. The “Account” table, with these columns:

   1. Id

   2. SystemModstamp

   3. Name

2. An “Account__part1” table, with these columns:

   1. Id

   2. BillingStreet

   3. BillingCity

   4. BillingState

Virtually any field can be moved to a partition if needed – with the exception of a small number of fields that require special handling.

• The “Id” database column is always present on every partition.

• A handful of fields are required to remain on the primary partition:

  • IsDeleted

  • Salesforce Modification Timestamps:

    • SystemModstamp

    • LastModifiedDate

  • Salesforce Audit Timestamps:

    • CreatedDate

    • LoginTime

    • SentDate

• Fields that are encrypted with the CS:Govern application.

Partitions may be created for virtually any object – the only exceptions are:

• BigObjects

• Tables that are included in the CopyStorm Database View system.

The CopyStorm/Medic Permission Checker will find Salesforce tables and fields that are visible using the Salesforce metadata API but are not visible for backup using the current Salesforce credentials.

It is a common misconception that a member of the Salesforce Administrator group can see all table and fields in a Salesforce instance. In practice, there are even many standard Salesforce fields that a Salesforce Administrator cannot view by default. The Permission Checker helps you find these tables and fields.

Running the Permission Checker in a GUI

Running the Permission Checker involves clicking on a single button – Start Scanning Metadata.

• The Salesforce user credentials provided to the Permissions Checker must have access to read the Salesforce metadata definitions for each object.

• Use of the metadata API requires the system permission “Modify Metadata Through Metadata API Functions.”

Do not be alarmed when you find a lot of fields that are not visible to a Salesforce Administrator. Most likely you are not using them in your instance.

Running Permission Checker as a Batch Job

The Permission Checker tool can be run as a batch job using the following syntax:

• Windows

  • CopyStormMedic.bat -tool findPermissionErrors -run configFile.copyStormMedic

• Linux

  • sh CopyStormMedic.sh -tool findPermissionErrors -run configFile.copyStormMedic

To configure a S3 partition in CopyStorm/Medic, provide connection information for the target S3 bucket.

CopyStorm will use the AWS Default Credential Provider Chain to connect to S3. Environment variables, Java Properties, IAM Roles, etc… can be used to authorize CopyStorm for S3 access.

To initialize the partition, CopyStorm/Medic will:

• Copy field data for the selected fields into the target bucket, using the folder name as a prefix.

• DROP the database storage used by the selected fields.

When CopyStorm replicates new or updated records, field values for the selected fields will be kept in files in the target bucket.

Record data is kept in a nested structure generated using Salesforce Ids.

The CopyStorm Snapshot tab is the launch point for creating, editing, using, and exploring CopyStorm snapshot data sources. A CopyStorm Snapshot is a database store containing tamper-proof snapshots of records from any Salesforce table where the snap-shots are taken based upon rules specified by a user.

CopyStorm Snapshots can be used to store many types of data.

• A Snapshot can be used to create a unlimited duration recycle bin or a recycle bin where data retention policies vary by table.

• A Snapshot can be used to permanently save a copy of Salesforce records on demand.

• A Snapshot can be used to track all versions of critical Salesforce records.

Each CopyStorm Snapshot has policies which determine which table and which records should be snapshot. Like the core CopyStorm backup process, Snapshot policies can be applied automatically as a regular part of a backup or can be applied on demand.

The CopyStorm Snapshot GUI

The CopyStorm Snapshot tab lists all currently defined snapshots, can be used to create a new snapshot, and is the launch point for most Snapshot operations including:

• Edit a Snapshot Policy’s Rules

• Explore the data in a Snapshot

• Delete a Snapshot Policy and its data

• Run a Snapshot Policy on demand.

• Apply a Snapshot Policy’s data retention rules.

Next Steps

To get started with CopyStorm Snapshots, please review the following:

• CopyStorm Snapshot System Overview

Once you have the basics down try one of these tutorials:

• How to Setup the Capstorm Recycle Bin

• How to Setup a Custom Snapshot Policy

• How to Explore and Export Snapshot Data

Finally, jump to any of the following reference articles:

• Snapshot Policy Editor

• Refresh Snapshot Statistics

• Run a Snapshot Policy

• Delete a Snapshot Policy

• Snapshot Explorer

A Snapshot Policy Filter Rule contains the logic which determines if a database record should be processed by the snapshot policy. There are several types of policy filters built into CopyStorm Snapshot.

• All Changes — this filter always indicates that a record should be processed. Use it when all changes to records should be snapshot.

• Column Expression — with this filter a rich expression based on columns in a record can be used to determine if a record should be snapshot.

• Deleted Records — this filter only matches records that have been deleted in Salesforce. This filter is used to build recycle bin type policies.

• SQL Predicate — this advanced filter supports injecting user specific SQL fragments into a query that determines if a record should be snapshot.

The Filter Rule for a Snapshot Policy is specified on the Main Tab of the snapshot editor.

Parameters specific to the selected Filter Rule Type can be set on the Filter Rule Parameters tab.

White and Black Lists

Nearly all Filter Rule Types support the concept of a White and/or Black list for selecting the tables to be processed by the policy.

• A White List indicates tables to include in the snapshot.

• A Black List indicates tables to exclude from the snapshot. All tables not on the black list will be included.

Typically the technique used for selecting tables for a snapshot is either by White List or Black List. However, if you are brave (and using regular expressions) both a White and Black list may be useful.

The following example (from the All Changes filter rule type) illustrate the components of the White/Black list editor.

• The pull-down menu indicates whether tables should be selected by White list, Black list, or both.

The dialog for selecting tables is nearly the same for a White or Black list. The following example is for a White list.

The Snapshot Explorer is a tool for viewing and exporting data from a CopyStorm Snapshot. The tool is launched from the CopyStorm Snapshot tab.

The Snapshot Explorer has four stages, indicated by the navigation bar at the top of the dialog.

• Start — Select the snapshot to explore.

• Select Table — Select a table from the Snapshot to explore.

• Preview — Build a query and preview the selected data from the Snapshot.

• Export — Export the data from the snapshot in a variety of formats.

Start Stage

Select Table Stage

In the Select Table stage, select the table you wish to explore and optionally select the columns in the table to view. Note that you can always return to this stage in order to update the selected columns.

Preview Stage

In the Preview Stage, optionally create a filter that limits the records to view or export. The filter is created by dragging and dropping elements from the left hand side of the screen to the big white box on the right.

In the example screen shot a filter for finding records with a capstorm email domain has been specified and then the Refresh Preview button was clicked.

Export Stage

In the Export Stage:

• Review and update the list of columns to export.

• Click on the Start Snapshot Export button and the system will display a typical Save As dialog.

By default, only the most recent version of a record will be written in the export. To override this behavior and write ALL VERSIONS of a record to the export check the box to Include All Snapshot Record Versions of a Salesforce Record. Of course, even if this option is checked, a record still has to pass the filter criteria from the Preview stage.

The Save As dialog which appears after clicking on Start Snapshot Export will look like your system’s default dialog with the addition of a file format selection section.

When the result of an export is loaded into your favorite spreadsheet it will look like:

The Run Snapshot Policy feature is used to run a snapshot policy against existing data in a CopyStorm snapshot on demand. This process is useful in a number of circumstances.

• Suppose a new snapshot for all Cases older than 2 years was just defined. Before activating the snapshot policy (so it will run automatically when a backup happens) you may want to snapshot all existing cases in the CopyStorm database that are older than 2 years.

• Your compliance department has told you to snapshot all records related to ACME, Inc. because they have filed a breach lawsuit against the company. In this case the snapshot policy that you define may run just once — on demand — and never be scheduled to run automatically.

The Run Snapshot Policy feature is accessed from the right-click context menu on the CopyStorm Snapshot tab.

There are just a few options to run a Snapshot Policy by hand.

• Scan Records From sets a start date from which records will be searched. By default the start date will be the date of the earliest record in the corresponding CopyStorm table.

• Scan Records Thru sets the last date on which records will be searched. By default the end data will be the most recent date in the CopyStorm database.

• # Concurrent Searches limits the number of tables that will be processed concurrently. You can set it to a large value (like 200) but be aware that a large number will consume considerable database resources.

• # Searches Per Batch limits the number of records that will be evaluated by the snapshot policy concurrently (on a per table basis). A larger number will increase performance slightly at the cost of making the user interface a bit less responsive.

• # Snapshot Per Batch limits the number of records that will be written to a snapshot in a single database transaction.

While a Snapshot Policy is running the screen will continuously update with progress information. At any point in time you can cancel the snapshot process and only records snapshot so far will be preserved in the database.

The following screenshot illustrates how the dialog will appear after a snapshot policy has run to completion. Note that are several reasons that a record may be processed but not snapshot.

• If a record fails to match the filter associated with the snapshot policy then it will be skipped.

• If a record matches the filter associated with the snapshot policy but is already in the snapshot with exactly the same data then it will be skipped.

The Batch File tab is used to generate a script that can be used to launch CopyStorm as a batch job. Generated scripts encapsulate everything needed to run a CopyStorm job, including:

• The location of CopyStorm.

• The location of the configuration file defining backup parameters.

For many sites a generated batch file is not necessary due to built-in command line options in the main CopyStorm launch script allowing batch mode execution. Example command lines include:

• sh CopyStorm.sh -run /path/to/my/configFile.copyStorm

• CopyStorm.bat -run C:\path\to\my\configFile.copyStorm

The Core Tool

The core function of the Batch File tab is to generate a script file that will:

• Run CopyStorm using a specific configuration file from any directory using just the name of the script file.

• Capture any exception information from CopyStorm to “do something” with the data. By default the script emails exception information to a user but this can easily be adapted to other purposes.

If you have configured the Notifications tab, the process for generating a script file is simple. This is the approach used nearly 100% of the time:

• Use the “Script Format” menu to specify the desired scripting language — bash, bat, python, or vbs.

• Click on the “Generate Script” button.

• Click on the “Save Script” button and save the script to a file.

If your site cannot use the built-in Notification system the process for generating a script file is:

• Use the “Script Format” menu to select one of the script formats marked as Advanced.

• Enter an email address for the notification sent each time the script runs.

• Enter an email address for the notification sent if CopyStorm reports an exception.

• Enter the name of your local SMTP server.

  • In the example a public server hosted by Google is used.

• Enter the email address that will send the email.

• Click on the “Generate Script” button.

• Click on the “Save Script” button and save the script to a file.

• Edit the generated script’s code to implement your custom notification requirements.

After these steps are completed, you own the generated script file and can change it in any way you prefer.

If you have trouble getting the email portion to work properly, please read this FAQ.

Initial Steps

Before running CopyStorm for the first time, create a dedicated Snowflake database for your Salesforce data.

Connection String

Compared to the other Database types, Snowflake’s connection string is more complex and carries more information. For this reason, it is recommended to use the Connection String Builder to create a valid Connection String.

Connection String Builder

After clicking on the “Connection String Builder” button, a dialog menu will open, as seen below.

The Field History Configuration editor is used to enable and configure CopyStorm field history tracking.

CopyStorm field history tracking supports the tracking of nearly all field changes in every Salesforce object.

If the “Track Field History” checkbox is selected, the default behavior is to track all changes to all fields. To limit field history tracking or to customize retention periods for field history changes, click on the “Edit” button for advanced Field History configuration.

Field history changes are kept in a database table named “CopyForceFieldHistory”. For details see:

• CopyStorm Logging Tables

Field History Editor

Field History is controlled by three major elements:

• Whitelist — A Whitelist controls which tables (and optionally fields) participate in field history. An unlimited # of white lists can be defined.

• Blacklist — A Blacklist controls which tables DO NOT participate in field history

• Retention Days — Retention Days controls how many days CopyStorm should keep field history. This can be control on a per table basis and can range from 1 day to forever.

To setup Field History rules there are several decisions to make:

• Do I want to control what tables are backed up using a Whitelist or Blacklist approach?

• By default, how many days should field history records age before they are automatically deleted by CopyStorm?

• Are there any tables whose field history records I want to keep longer (or shorter) than the default?

The Field History Editor has several major components:

• Global Parameters specify constraints applied to all rules.

• The Whitelist/Blacklist editor indicates which tables should (or should not) participate in field history tracking.

Global Parameters

There are several global field history parameters.

• Include Formula Columns parameter controls where changes to formula fields are recorded as field history changes. The default is false.

• The Exclude Audit Fields parameter restricts common Salesforce auditing fields from being records in field history. There are several reasons to exclude an audit field.

  • A field is a timestamp field that changes on every record change.

  • A field is Salesforce maintained formula field (example: LastViewedDate)

  • A field is also recorded as separate column in CopyStorm’s field history table.

To change the list of excluded audit fields click on the Edit… link the left of the field label.

Whitelist/Blacklist Editor

The Whitelist/Blacklist part of the editor is used to manage what tables participate in field history tracking.

• The Whitelist indicates what tables should participate in field history tracking. If the whitelist is empty this means that all tables except those in the blacklists will be tracked.

• The Blacklist indicates what tables should not participate in field history tracking.

Of course, in practice either the whitelist or blacklist is used but not both. When the white list is not empty the blacklist is effectively ignored.

Before configuration the editor looks like:

Select specific tables and/or table patterns to add to the white list by clicking on the appropriate button.

If the default field retention rule is too short (or long) for specific tables, an extra white can be added to change the retention rule. The example below changes the retention rule for Account and Contact to 1000 days.

Limit Fields Subject to Tracking

It is possible to limit the fields tracked for a specific table by edit the table’s rule. Of course, doing this means that any new fields added to a table will not be tracked by default.

The following image illustrates how to limit the fields tracked for the Account table.

The Database Networking Configuration Editor contains networking and SQL parameters used when communicating with the CopyStorm database.

The Database Query Optimization Parameter Editor controls optimizations used when generating SQL queries for the CopyStorm database.

The Salesforce Network Configuration Editor contains networking and SQL parameters used when communicating with Salesforce.

The CopyStorm/Medic Global Parameters page sets parameters that apply to all CopyStorm/Medic jobs.

The Global Parameters page can be found by selecting the “Other Tools” dropdown in CopyStorm/Medic, and clicking “Global Parameters” in the dropdown list.

To configure a Folder partition in CopyStorm/Medic, provide the target folder:

To initialize the target directory, CopyStorm/Medic will:

• Copy field data for the selected fields into the target folder.

• DROP the database storage used by the selected fields.

When CopyStorm replicates new or updated records, field values for the selected fields will be kept in files in the target directory.

Record data is kept in nested folders generated using Salesforce Ids.

The Deleted Records Filter Rule indicates that a CopyStorm record should be snapshot only when its IsDeleted flag is set to true. The primary use of the filter is to manage the records added to a recycle bin.

Though CopyStorm is initialized with a policy, Default Recycle Bin, that uses this filter rule, a user can added additional snapshot policies based on a Deleted Records Filter Rule if a non-standard recycle bin is desired.

The only configuration for a Deleted Records Filter Rule is which tables to include in the snapshot.

This page provides details into the command line options for running snapshot policies for CopyStorm in headless mode. This feature was introduced as part of the 10.65.1 GA release of CopyStorm. For more information regarding running snapshot policies, see here.

Overview

The command line functionality mirrors the same options and functionality as seen in the CopyStorm application using the "Run Snapshot Rules Now" option including:

• Start and end date ranges for records to snapshot

• Number of concurrent searches (number of objects snapshot in parallel)

• Search batch size

• Snapshot per batch

Command Line Switches

Example Usage

This example is for Windows installations of CopyStorm. For Linux/Mac installations, change CopyStorm.bat to CopyStorm.sh

CopyStorm supports the tracking of nearly all field changes by checking the “Track Field History” box on the Advanced tab. When field history is enabled, default behavior is to track updates to EVERY field in EVERY table except for a handful of Salesforce auditing fields.

If the default behavior captures more data than your organization prefers, this article explains how to limit what is tracked.

Background

Fine control over field tracking is managed by a configuration file named FieldHistoryRecordFactory.xml. The file can be placed in either the “config” subdirectory of the CopyStorm installation or in the directory indicated in the CopyStorm configuration file. The built-in configuration file has rules excluding field tracking for a number of Salesforce audit fields and all formula columns.

It looks like:

If you create your own configuration file, model it after the built-in file without repeating any of the built-in rules.

Like CopyStorm table selection, there are two basic approaches to controlling which fields are tracked:

• Telling CopyStorm which fields should be tracked.

• Telling CopyStorm which fields should NOT be tracked.

If CopyStorm is told which fields to track the rules indicating which fields should NOT be tracked are ignored and only fields CopyStorm is explicitly told to track will be included.

Include Specific Tables

To only track field history on specific tables, include the IncludeTable configuration directive.

The following FieldHistoryRecordFactory.xml will limit field history to the Account, Contact, and MyCustomObject__c tables:

Exclude Specific Tables

To track fields in all tables EXCEPT for a few then use the ExcludeTable configuration directive.

The following FieldHistoryRecordFactory.xml will exclude ApexPages, ApexTriggers, and MyCustomObject__c from field history tracking:

Include Specific Fields in a Table

The following FieldHistoryRecordFactory.xml will cause CopyStorm to only track the “Name” field on “Account” and the “FirstName”/”LastName” fields on “Contact”:

Exclude Specific Fields in a Table

The following FieldHistoryRecordFactory.xml will cause CopyStorm to not track the “WebSite” field on “Account”, and to not track the “Age”/”Birthday” fields on “Contact”:

Track Formula Value Changes

Default CopyStorm behavior is to not track changes to formula column values. The following FieldHistoryRecordFactory.xml will enable formula value history tracking:

Though MySQL claims support for all UTF-8 characters, experience indicates that not all MySQL variants support the full extended 4 byte UTF-8 character set. When MySQL fails to recognize an extended UTF-8 character and error like this will be displayed:

• java.sql.BatchUpdateException: Incorrect string value: ‘\xF0\x9F\x98\x8A’ for column ‘CommentBody’ at row 48

To get around this issue, you can configure CopyStorm to replace unsupported 4 byte UTF-8 characters with a substitution.

Note: The extended 4 byte UTF-8 character set contains like 100 variations of a smiley face, trees, hamburgers, etc…

How to Define Character Mappings

Character mappings are defined in a “UTF8Tool.xml” configuration file that can be placed in either the “config” subdirectory of the CopyStorm installation or in a custom config directory you have created. The following UTF8Tool.xml will cause CopyStorm to replace the 4 byte UTF-8 characters for two smiley faces to be converted to their more old fashioned counterparts:

This rule will cause any text containing the specified smiley face characters to have the characters be replaced by “:)” or “:(” before data is written to the database.

How to Convert Hex Strings to UTF Characters

Since MySQL reports UTF-8 problems using a hex byte sequence, you will need a way to convert hex byte sequences to a character suitable for XML. Capstorm has used the tool located at:

• http://www.endmemo.com/unicode/unicodeconverter.php

To use the tool enter the hex digits from MySQL into the box labeled “UTF-8 Code” (e.g. 20 E2 88 9A) and press the Convert button. Copy the generated unicode character into the “from” parameter in UTF8Tool.xml. Note that you can use the regular UTF-8 character in the “from” parameter rather than the decimal or hex representation.

The Salesforce API Configuration Editor is used to choose which Salesforce API will be used to copy data.

Data API Options

The Data API Options section is used to configure the primary Salesforce API that will be used to copy data.

• Salesforce Threads

  • The number of Salesforce Objects that will be backed up at the same time by CopyStorm.

  • The default number is 3, allowable values are 1 – 20. For maximum performance, the optimal value for this parameter is typically 10.

• Salesforce API

  • The default API to use when querying Salesforce. The default API is REST.

  • Note that CopyStorm will, by default, use REST of any table containing a BASE64 column because this minimizes memory usage for most databases. Common BASE64 tables include Attachment and ContentVersion.

Bulk API Options

The Bulk API parameters section controls when CopyStorm will use the Salesforce Bulk API rather than the default API.

Note that:

• A Salesforce organization has a limited number of Bulk API calls per 24 hour rolling period. Be aware of your limit.

• The SOAP or REST API will almost always perform better than the Bulk API if performance is measured in the amount of time a user has to wait for a backup to complete.

  • We suspect that this is because Bulk API queries run on a reduced priority thread within the Salesforce infrastructure.

Metadata API Options

The Metadata Data API Options section is used to configure the primary Salesforce API that will be used to copy Metadata.

• Metadata Threads

  • The number of Salesforce Metadata Types that will be backed up at the same time by CopyStorm.

  • The default number is 5, allowable values are 1 – 10. For maximum performance, the optimal value for this parameter is typically 5.

The Maintenance Configuration Editor is used when troubleshooting replication issues.

The SOQL Query Optimization Parameter Editor controls optimizations used when generating SOQL queries for Salesforce.

A Column Expression Filter Rule is a rich graphical tool for specifying the conditions under which a record should be added to a CopyStorm snapshot. The interface is patterned after the Salesforce Report Writer expression builder and should feel familiar to most users.

The first step, after selecting the Column Expression option from the Filter Rule Type pull-down, is to launch the column expression filter editor.

Clicking on the Edit Column Expression Filter button will launch the editor.

The following example creates a filter that finds records where:

• the Name field contains “Louis”

The screen image shows the dialog after the Refresh Preview button has been clicked. Notice that the searched database has multiple accounts with the similar names that matched the filter.

After saving changes and returning to the policy editor, a summary of the filter will be shown. To change the filter simply click on the Edit Column Expression Rule again.

Common Issues

Issue 1: Very few columns are available for selection

The list of available columns is determined as the intersection of all columns from table names matched by the policy’s Include Tables list. If you see a single column named Id then the policy is including all tables in Salesforce and the only common column name is Id. To increase the number of available columns return to the policy editor page and adjust the list of included tables.

Issue 2: No Records appear when I click the Refresh Preview button.

This issue only occurs when there are no matching records in the CopyStorm database in any of the selected tables. The solution is to modify the query.

The CopyStorm/Medic Fix IsDeleted tool identifies records that have been deleted in Salesforce, removed from the Salesforce recycle bin, but not marked as deleted in CopyStorm.

This situation occurs when:

• A user deletes records in Salesforce and empties the recycle bin BEFORE CopyStorm has run.

• A system administrator empties the global recycle bin.

• A Salesforce API program does a hard delete on Salesforce records. A hard delete bypasses the Salesforce recycle bin mechanism.

Under normal circumstances, when a user deletes a record in Salesforce the record is not really deleted immediately. Instead, it is placed in the Salesforce recycle bin. The mechanism used by Salesforce is a hidden field named “isDeleted”. When CopyStorm runs it copies all modified records — including those where the “isDeleted” flag is set to true. After records are copied by CopyStorm, they are optionally deleted in the CopyStorm database based on parameters set on the Advanced tab.

If normal Salesforce accounts are not permitted to empty their recycle bin (a good practice) then the condition that needs this tool will likely not occur.

Running Fix IsDeleted in a GUI

The UI for the Fix IsDeleted tool contains three parts:

• The side of the dialog is used to select the tables to scan.

• The top part of the dialog has parameters that determine if problem records should be fixed.

• The lower part of the dialog displays the progress of a scan as it happens.

Running Fix IsDeleted as a Batch Job

The Fix IsDeleted tool can be run as a batch job using the following syntax:

• Windows

  • CopyStormMedic.bat -tool fixDeleted -run configFile.copyStormMedic

• Linux

  • sh CopyStormMedic.sh -tool fixDeleted -run configFile.copyStormMedic

Viewing Identified Problem Records

By checking the “Show Problems” checkbox, the application will store a list of all record IDs that are identified as being nonexistent (deleted) in Salesforce. To view the record IDs, run the tool, and after it is completed click the “Show Records” button. This will open up a modal window displaying the Table name, and Salesforce Record ID of all the records that were identified by the tool.

The “Show Problems” setting can be used to validate the data prior to anything being deleted. To use it in this way, ensure that “Show Problems” is enabled, and “Fix Problems” is disabled. With this configuration, the tool will search for and identify problem records, and generate a list of IDs that can then be used to query the backup database to validate data prior to deletion, or for use in identifying mistakenly deleted records in Salesforce.

In the Record viewer window, the resulting list of TableName:RecordID pairs can be exported as a .csv as well, using the “Export” button at the bottom of the modal window.

See below for an example of the record viewer window.

Viewing Identified Problem Records – When Running Via Terminal

When running the tool via command line, and using a config file which has the “Show Problems” flag enabled, the command line output of the application will include a list of all identified problem records. An example of this is shown below.

The CopyStorm/Medic Refresh Columns tool replaces the value of one or more columns in CopyStorm with their current values in Salesforce.

This tool is typically used when the definition of a formula is modified in Salesforce or when a previously hidden column becomes visible to the user CopyStorm uses to authenticate with Salesforce.

When one or more columns in CopyStorm do not match the current value in Salesforce there are two methods which can fix the problem:

• Perform a forced update on the CopyStorm database using CopyStorm. This technique works but will cause every column in the CopyStorm tables to be read from Salesforce and written to CopyStorm, including columns whose data is already synchronized.

• Use this tool to limit reads/updates from Salesforce to specific columns. This technique can be many times faster than a forced update, and does not re-synchronize data that is already synchronized.

Running Refresh Columns in a GUI

To access the Refresh Columns GUI:

• Select “Other Tools” from the top-level menu.

• Select “Data Synchronization”, then “Refresh Columns”.

The UI for the Refresh Columns tool contains three parts:

• The side of the dialog is used to select the tables and columns to scan.

  • Click on a table name to launch a pop-up dialog for column name selection.

• The top part of the dialog has parameters that determine the date range of records to be updated.

  • Leave these parameters blank to scan all records.

• The lower part of the dialog displays the progress of a scan as it happens.

Clicking on a column name will cause a column selection dialog to appear:

Managing Stale Formulas Automatically

When a formula column definition changes in Salesforce, timestamps on Salesforce records do not change. In practice this means that a formula column in the CopyStorm database will not reflect the proper value after a formula definition change until the next time a non-formula field on the record changes.

Starting with CopyStorm 7.34.10, both formula column definition changes and the last formula column refresh time are tracked. The “Manage Stale Formulas Automatically” option takes advantage of this tracking and, when checked, will:

• Automatically identify all formula columns whose definition has changed since their last refresh in CopyStorm, and add them to the column refresh set.

• Once the formula columns are refreshed, CopyStorm will update the formula refresh time in its tracking tables.

Running Refresh Columns as a Batch Job

The Refresh Columns tool can be run as a batch job using the following syntax:

• Windows

  • CopyStormMedic.bat -tool refreshColumns -run configFile.copyStormMedic

• Linux

  • sh CopyStormMedic.sh -tool refreshColumns -run configFile.copyStormMedic

The CopyStorm/Medic Refresh Formulas tool replaces the values of formula columns in CopyStorm with their current value in Salesforce.

This tool is typically used when the definition of a formula is modified in Salesforce.

CopyStorm minimizes the amount of records copied from Salesforce by only copying records whose modification date has changed. When a formula column is modified in Salesforce record modification times do not change so CopyStorm does not pick up the new formula values for older records. There are two ways to update this data in the CopyStorm database:

• Performing a forced update on the CopyStorm database using CopyStorm. This technique works but will cause every column in the CopyStorm tables to be read from Salesforce and written to CopyStorm.

• Using this tool to limit reads and updates from Salesforce to formula columns. This technique can be many times faster than a forced update.

Running Refresh Formulas in a GUI

To access the Refresh Formulas GUI:

• Select “Other Tools” from the top-level menu.

• Select “Data Synchronization”, then “Refresh Formulas”.

The Refresh Formulas tool contains three parts:

• The side of the dialog is used to select the tables to scan. Note that if a table without formulas is selected it will be silently ignored by the tool.

• The top part of the dialog is used to specify the date range of records to be updated. Leave these parameters blank to scan all records.

• The lower part of the dialog displays the progress of a scan as it happens.

Running Refresh Formulas as a Batch Job

The Refresh Formulas tool can be run as a batch job using the following syntax:

• Windows

  • CopyStormMedic.bat -tool refreshFormulas -run configFile.copyStormMedic

• Linux

  • sh CopyStormMedic.sh -tool refreshFormulas -run configFile.copyStormMedic

The CopyStorm/Medic Scan Timestamps tool performs an exhaustive comparison of Salesforce versus CopyStorm record modifications timestamps. This tool is provided for two reasons:

• It can be used to provide assurance that CopyStorm is copying all records properly.

• If Salesforce has an “after update” trigger on a table where the trigger updates the source table, this tool can be used to help track down the problem trigger.

Comparing modification timestamps from Salesforce with CopyStorm is a trickier task than one might think — here are a few of the reasons why:

• Salesforce stores timestamps to the nearest millisecond.

• Microsoft SQL/Server stores timestamps to the nearest 3 milliseconds.

• MySQL stores timestamps to the nearest second.

• Database to GMT time conversion works differently on different databases.

The Scan Timestamps tool will report the table, record id, timestamps, and timestamp difference for each record in Salesforce whose timestamp does not match CopyStorm and for records that are missing from CopyStorm. Records may be missing from CopyStorm due to being missed in a sync or because they will be synced in the future.

Run Scan Timestamps in a GUI

To access the Scan Timestamps GUI:

• Select “Other Tools” from the top-level menu.

• Select “Database Validation”, then “Scan Timestamps”.

The UI for the Scan Timestamps tool contains four parts:

• The side of the dialog is used to select the tables to scan.

• The top part of the dialog has parameters that determine the date range of records to be updated. Leave these parameters blank to scan all records.

• The lower part of the dialog displays the progress of a scan as it happens.

• If an error is detected a button will appear in the lower right hand corner. Click on the button to see error details and/or export the errors to a file.

NOTE: We purposefully corrupted the CopyStorm database so the “Show Problems” button would appear in the following screen shot.

If the Show Problems button appears, it can be clicked to show the details for each problem record.

In this example we corrupted the CopyStorm database by hand so the Salesforce database would contain records missing from CopyStorm. The following screen shot is the result of clicking on the “Show Problems” button:

Running Scan Timestamps as a Batch Job

The Scan Timestamps tool can be run as a batch job using the following syntax:

• Windows

  • CopyStormMedic.bat -tool scanTimestamps -run configFile.copyStormMedic

• Linux

  • sh CopyStormMedic.sh -tool scanTimestamps -run configFile.copyStormMedic

The CopyStorm/Medic Table Editor tool is for non-DBA people who want to remove data or tables from a CopyStorm database. Why would someone want to do this?

• You may want to drop a specific table so CopyStorm will rebuild it the next time it runs. This case comes up when a column definition in Salesforce has changed and the corresponding change is not supported by the target database.

• You may want to drop a Salesforce Feed table, like AccountFeed, so CopyStorm will replace it with a database view the next time it runs.

• You may want to make CopyStorm completely forget about a database table it created. This action drops a table and all references to it in the CopyStorm metadata.

Without the Table Editor tool a DBA can always manually truncate or drop a table — this tool is designed to provide a way for a non-DBA to perform the same task.

Running Table Editor in a GUI

To access the Table Editor GUI:

• Select “Other Tools” from the top-level menu.

• Select “Rarely Used”, then “Table Editor”.

The UI for the Table Editor tool contains three parts:

• The side of the dialog is used to select the tables to truncate, delete, or forget..

• The top part of the dialog determines if the selected tables will be deleted, truncated, or forgotten.

• The lower part of the dialog displays the progress of table modifications.

Running Table Editor as a Batch Job

The Table Editor tool cannot be run as a batch job.

Find Dirt

The CopyStorm/Medic Find Dirt tool finds data in Salesforce fields that should not be possible. Capstorm provides this tool because, as a Salesforce instance ages, it will often accumulate data that does not match the field definition. Here are a few examples:

• A string field is limited to 80 characters but has a few records where 96 characters are stored.

• A numeric field is limited to zero digits after the decimal point but Apex code inserted records where digits appear.

This tool will report the table, record id, column name, and problem description for any field that does not match the Salesforce field definition.

Running Find Dirt in a GUI

To access the Find Dirt GUI:

• Select “Other Tools” from the top-level menu.

• Select “Salesforce Data Quality”, then “Find Dirt”.

The UI for the Find Dirt tool contains four parts:

• The side of the dialog is used to select the tables to scan.

• The top part of the dialog has parameters that determine the date range of records to be scanned.

  • Leave these blank to scan all records.

• The lower part of the dialog displays the progress of a scan as it happens.

• If an error is detected a “Show Problems” button will appear in the lower right hand corner.

  • Click the button to see error details and/or export the errors to a file.

If the “Show Problems” button appears, click it to show the details for each problem record.

Running Find Dirt as a Batch Job

The Find Dirt tool can be run as a batch job using the following syntax:

• Windows

  • CopyStormMedic.bat -tool findDirt -run configFile.copyStormMedic

• Linux

  • sh CopyStormMedic.sh -tool findDirt -run configFile.copyStormMedic

The Logging Configuration Editor is used to configure CopyStorm logs that are written to the database, STDOUT / STDERR, or using the Apache Commons Logging framework.

When the Salesforce REST API returns a record with a BASE64 column, the BASE64 data is returned as a hyperlink that can be used to read the raw bytes. Experience has shown that it is possible for the hyperlink returned by Salesforce to return a HTTP 404 (not found) or an HTTP 500 (internal lightning platform error) rather than the expected HTTP 200 (OK). When this happens:

• The BASE64 data cannot be read by the REST API.

• The same operation using the SOAP API results in an UNKNOWN_EXCEPTION or an unparsable SOAP body

The Base64 Sanity Checker tool tests the hyperlinks returned by the REST API and reports the ID of records where the hyperlink fails (e.g. Does not return a HTTP 200). It is often wise to check each reported record using the Salesforce GUI to make sure that the data is really available. If you find a corrupted Attachment record report it to Salesforce.

Running The Base64 Sanity Checker in a GUI

To access the Base64 Sanity Checker GUI:

• Select “Other Tools” from the top-level menu.

• Select “Salesforce Data Quality”, then “Base64 Sanity Checker”.

The only required parameters are the tables to search, but there are a number of other parameters you may want to modify.

The next step is to click on the Start Sanity Check button and wait for the analysis to complete. Note:

• Each hyperlink check takes one Salesforce API call. There is one additional API call for every 2000 records.

• An running analysis can be cancelled but the cancellation process may not be immediate. The reason is that pending HTTP requests must complete or timeout before the cancellation process is finished.

Hopefully when you scan your Salesforce the tool will not find potential problems. However, if potential problems are found then it is wise to check the validity of each record using the Salesforce GUI.

If you are using CopyStorm to perform backups, then this class of error will be discovered during the normal course of a backup. Unfortunately the error is often masked by an UNKNOWN_EXCEPTION or an Invalid SOAP Envelope response from Salesforce.

The Fix DocLinks tool repairs records in the Salesforce ContentDocumentLink table. The Salesforce ContentDocumentLink table has query restrictions that make it impossible to efficiently perform an 100% accurate incremental backup. There are two primary reasons that block an 100% accurate backup.

1. The ContentDocumentLink table cannot be queried only on its SystemModStamp field.

2. ContentDocumentLink permission values can be changed and there is no way to find the changes efficiently because of reason #1.

In practice most customers will either never run this tool or run it on a weekly basis.

Running Fix DocLinks in a GUI

To access the Fix DocLinks GUI:

• Select “Other Tools” from the top-level menu.

• Select “Data Synchronization”, then “Fix DocLinks”.

The UI for the Fix DocLinks tool is minimal.

• The top of the dialog controls how many concurrent queries to run against Salesforce and what data ranges to scan.

• The middle part of the dialog has a large button for starting the search and text for reporting progress

The tool will report progress as it scans records and when finished will look like:

where:

• Candidates = total # of ContentDocumentLink records scanned

• Inserted = number of ContentDocumentLink records inserted into the CopyStorm database.

• Update = number of ContentDocumentLink records updated in the CopyStorm database.

The Fix DocLinks tool can be run as a batch job using the following syntax:

• Windows

  • CopyStormMedic.bat -tool fixDocLinks -run configFile.copyStormMedic

• Linux or Mac

  • sh CopyStormMedic.sh -tool fixDocLinks -run configFile.copyStormMedic

The CopyStorm/Medic Count Records tool performs a comparison of object record counts in Salesforce versus CopyStorm. This tool is provided as a sanity check for backup validation, to provide assurance that CopyStorm is copying all records properly.

Run Count Records in a GUI

To access the Count Records GUI:

• Select “Other Tools” from the top-level menu.

• Select “Database Validation”, then “Count Records”

The UI for the Count Records tool contains four parts:

• The left-hand side of the dialog is used to select the tables to scan.

• The top part of the dialog has the following parameters:

  • of Tables to Process Concurrently.

    • Specifies how many tables the tool will process in parallel. Higher values may improve performance but increase resource usage.

  • Output To CSV (checkbox).
• The lower part of the dialog displays the progress of a record count analysis as it happens.

• If an error is detected, a button will appear in the lower right hand corner. Click on the button to see error details and/or export the errors to a file.

• Windows

  • CopyStormMedic.bat -tool countRecords -run configFile.copyStormMedic

• Linux / Mac OS

When partitioning tables containing large volumes of data or Base64-encoded content, the partition creation process can fail mid-way due to various issues, such as:

• Application freezes or crashes

• Transient network errors

• Resource exhaustion (e.g. memory or CPU spikes)

• Timeouts or I/O bottlenecks

These interruptions can leave partitions in an incomplete or inconsistent state.

The Partition Creation process is designed to be incremental:

• Records are copied from Source to Target Partition in an ID-based ordering (lexicographical).

• After each batch of records is copied from Source to Target, the last read Salesforce ID is persisted in the Database.

  • See the CopyForceTablePartitionLoadTask database table.

Note: CopyStorm will refuse to replicate data to a table if it has a Partition Creation In-Progress

If partition creation fails, you can resume the process by navigating back to the Partition Editor Tool. You’ll see the following dialog appear if a partition creation is in-progress:

From this point, you’ll have the option to:

• Resume the Partition Creation process

  • This will start copying data from Source to Target from the Last Read Salesforce ID.

• Cancel the Partition Creation process

  • Note: Cancelling will not remove data already copied if the Source is S3 or a Folder

The Main Tab is used to manage connection information and credentials for Salesforce and the target database.

Specifies the type of target database. Available database targets depend on the version of CopyStorm you have installed.

These parameters are used when authenticating with Salesforce using a Username and Password.

• For certificate based authentication, see the .

• For SSO-based authentication, see .

The Database Schema Configuration Editor contains parameters used to configure CopyStorm database schema generation.

The Core Configuration Editor is used to specify which objects should be replicated to the database, and to control how long records will be retained in the database after being deleted in Salesforce.

The “Select Objects” dropdown selects the objects which will be copied from Salesforce to the database.

The easiest way to configure which objects to copy is to select one of the pre-configured options:

• All Objects

  • Copy all Salesforce objects that the CopyStorm Salesforce User has access to.

On rare occasions a site may receive an UNKNOWN_EXCEPTION from CopyStorm. In CopyStorm’s log, the error will look like:

10-Feb-17 09:02:11: [UnexpectedErrorFault [ApiFault exceptionCode=’UNKNOWN_EXCEPTION’ exceptionMessage=’An unexpected error occurred. Please include this ErrorId if you contact support: 303701882-51722 (1187285824)’ extendedErrorDetails='{[0]}’

This error is generated by Salesforce’s server when it encounters a problem that Salesforce code does not know how to handle. Most of the time the issue will resolve itself after a couple days as Salesforce fixes their own internal problems. If the problem does not clear up automatically then this article may help you resolve the issue.

In Capstorm’s experience, to get Salesforce support to pay attention to UNKNOWN_EXCEPTION issues a problem has to be reproduced in an application supplied totally by Salesforce. When this error is being given by Salesforce we need to:

• Isolate the SOQL query in CopyStorm that is causing the problem.

The CopyStorm/Medic Lineage Maintenance Tool is used to view lineage data & invalidate problematic lineage records.

Over time, a Record’s lineage may be invalidated:

• One of the legacy lineage entries may no longer exist in the CopyStorm Database or Snapshot.

• Records may have multiple children, causing split lineage.

For more details on Record Lineage, .

When CopyStorm writes data to an S3 partition, each Salesforce record is stored as one or more files in your S3 bucket. The location of every file is derived entirely from the Salesforce record ID. There are no date folders, job ID folders, or table name folders.

The folder hierarchy is constructed by slicing the first 13 characters of the Salesforce record ID into five segments:

As of CopyStorm/Medic 10.54.2, the Base64 Sanity Checker function has been merged into CopyStorm/Medic.

For the latest documentation, please see:

When the Salesforce REST API returns a record with a BASE64 column, the BASE64 data is returned as a hyperlink that can be used to read the raw bytes. Experience has shown that it is possible for the hyperlink returned by Salesforce to return a HTTP 404 (not found) or an HTTP 500 (internal lightning platform error) rather than the expected HTTP 200 (OK). When this happens:

Each time CopyStorm runs it records job information and statistics into a set of history logging tables within the CopyStorm database. The History Tab supports viewing and exporting CopyStorm job history.

The History Tab consists of two sections — a top section used to search for past CopyStorm jobs and a bottom section displaying a one-line status for each job in the database.

CopyStorm Job History

Job History Table Headers

When the “View…” column is clicked a dialog appears providing detailed job status. This dialog is similar to the “Copy in Progress” GUI but only contains tables that had activity in the selected job.

Any exceptions that occurred during the job will be displayed in the Exceptions tab. In this example, the job had a single exception.

When the “View…” column is clicked a dialog appears providing detailed job status:

In addition to viewing historical job data, the History Tab can be used to delete job records and to export job data to Excel-compatible CSV files.

To delete records:

• Click on the check box next to one or more Job #.

• Click on the “Delete” button.

To export job history to CSV files:

• Click on the check box next to one or more Job #.

• Click on the “Export” button.

The Notifications Tab contains parameters used to control who receives a status email when a CopyStorm/Medic job runs and under what circumstances email is sent. CopyStorm/Medic supports email notifications via SMTP or via a Salesforce SOAP API call.

SFDC Notifications

CopyStorm Snapshots support an unlimited number of Snapshot Policies with each Policy defined by the Snapshot Policy Editor. The following image lays out the primary sections of the Snapshot Policy Editor.

There are several tabs on the Snapshot Policy Editor.

• The Main tab contains properties common to all snapshots and is where the Filter Rule Type for the snapshot is indicated.

• The Filter Rule Parameters tab contains filter specific data. For example:

  • Most filter types support selecting which tables to include in a snapshot.

  • The Column Expression filter type supports rich user supplied expressions to limit which records are snapshot.

• The Advanced tab contains parameters rarely modified by a customer.

The following subsections describe each tab in details.

Each Snapshot Policy requires a unique name. There are no restrictions on what characters can appear in a name but the name itself must be unique for each snapshot policy.

A Snapshot Policy is always in either an Active or Inactive state.

• A policy in an Active state will be be used by CopyStorm as part of a normal backup. For example, the Default Recycle Bin policy will usually be set to an Active state so the backup will automatically snapshot records as they are deleted from Salesforce.

• A policy in an Inactive state can be ran by hand but will not be automatically processed as part of a CopyStorm backup. Inactive policies are useful for one-off compliance needs and snapshot policies that do not need to be part of a scheduled process.

The Apply Policy section controls when CopyStorm will check a record in the database against the policy filter. There are three options:

• After Insert — check a record against the policy immediately after it is inserted into the CopyStorm database.

• Before Update — check a record against the policy immediately before it is updated with changes from Salesforce.

• After Update — check a record against the policy immediately after it has been updated in the CopyStorm database.

One or all of the option can be selected concurrently. In all cases, CopyStorm will not add a record to a snapshot if an identical copy of the record is already in the snapshot.

To reduce the amount of database space required to snapshot a record, certain non-essential fields can be excluded. Common exclusion choices are:

• Empty Fields — when empty fields are excluded a savings of the “length of the field name” plus 3 bytes is saved for each of the record’s empty fields.

• Formula Fields — excluding a formula field can often save a lot of space in a record. Formulas are computed, so there is no data loss.

The # Days to Retain parameter controls how many days records are kept in a snapshot before they are deleted.

• Blank means to keep snapshot records forever.

• 90 means to delete snapshot records after 90 days.

In addition to the global # Days to Retain parameter, the policy for individual tables in a snapshot can be set explicitly by creating an override rule using the Advanced.. button. Clicking on the Advanced… button launches the .

The Filter Rule Type specifies specific properties that a record must have to be considered by the snapshot policy. Example Filter Rule Types include:

• Deleted Records — a record passes the filter if it has been deleted in Salesforce.

• Column Expression — a record passes the filter if the data in its columns matches a user defined expression.

• All Changes — a record always passes the filter.

For further details for each filter rule type, see the article on .

The content of the Filter Rule Parameters tab depends on the Filter Rule Type selected on the Main tab.

For example, the following screen shot is for a Column Expression filter rule.

• This filter says to only snapshot a record if it is a Contact or Account and is named CapStorm.

• Like most filters, the tables to be processed by the rule can be specified as a White List, Black List, or both.

For further details for each filter rule type, see the article on .

To boost performance of record relationship traversal, CopyStorm Snapshots maintain a high speed database index. This section supports excluding reference fields from the high speed index to save space in the database. The reference fields selected by default are rarely needed for relationship traversal, occur on nearly every record, and thus can take up significant space in the CopyStorm Snapshot index.

The CopyStorm/Medic Partition Editor tool modifies the schema of the CopyStorm database to vertically partition CopyStorm Salesforce data tables into multiple database tables.

This tool is typically used when a Salesforce object is wide enough that it cannot fit into a single SQL database table. For example, if the Salesforce Account and Contact objects both have the maximum number of custom fields and Person Accounts are enabled. This can cause a single Salesforce object to have more than 1,000 fields.

In the scenario where partition creation fails for any reason, the next partition creation attempt will pick up where the last partition creation failed. When a partition is in a partial state due to a failure, CopyStorm jobs will skip over backing up records to this table until the partition is completed.

This CopyStorm/Medic tool provides a way to resolve database error messages indicating that a record is too large to fit in a database table:

• MySQL: “Row size too large (> 8126)”

• SQL/Server: “Cannot Create a row of size 8765 which is greater than the allowable maximum row size of 8060”

• PostgreSQL: “row is too big”

To access the Partition Editor GUI:

• Select “Other Tools” from the top-level menu.

• Select “Partition Editor” in the dropdown.

A list of objects in the CopyStorm database is displayed on the left – select an object to view its partition configuration. After selecting an object, the following information will be displayed:

1. Partition Name

   1. The CopyStorm database table name for the table storing the field.

2. Field Name

   1. The Salesforce field name.

To move fields between partitions, or to create a new partition, click on the “Move Fields” button. This button will open a pop-up window displaying available partitions and configuration options to configure a new partition.

The “Move [Object] Fields To” drop-down controls the target partition.

• If a pre-existing partition name is selected, then any fields already in the target partition will not be available to select.

• If the “New JDBC Partition” option is selected, then CopyStorm/Medic will create a new database table to hold the selected fields.

Configuration parameters for the target partition type will be displayed below the drop-down.

Data partitions can be created in:

• The SQL Database, as a new JDBC Table.

The following screenshot displays the configuration panel for creating a new JDBC Database Table partition:

Clicking on the “Continue” button will open a pop-up for Field Selection:

• All selected fields will be moved to the target partition.

• The source partition for each field is displayed next to the field name.

After clicking on the “Review Changes” button, the selected fields will be displayed – highlighting the source partition(s) that currently contain the fields.

To perform the changes and update the CopyStorm database schema, click on the “Apply Changes” button. Clicking on the “Apply Changes” button will cause CopyStorm to:

1. DROP from the target partition any pre-existing columns for the selected fields.

   1. This is to ensure that the partitioning process can be retried should any issues be encountered during the partitioning process.

2. Create schema in the target partition.

While the partitioning process is running, status will be displayed in the “Log” tab. When the partitioning process has completed, the log will indicate that the job has finished.

After closing the pop-up, the Partition Editor will display the new status of the selected fields.

CopyStorm supports dynamic transformation of field data after reading it from Salesforce and before it is written to the target database. This article describes how to add a new custom transformation to CopyStorm.

History of This Feature

This issue first arose when a customer wanted to scramble Salesforce data for certain fields before it was written to the target database. Since the customer needed total control over the data manipulation process, an extension was added to CopyStorm to dynamically add field transformations. This allows you to write custom code to perform operations like:

• Scramble email addresses – instead of replicating plain-text email addresses, obfuscated emails could be replicated.

• Scramble sensitive data such as social security numbers or Contact health information.

Most customers do not require this feature — it is only used when Salesforce data needs to be modified prior to being added to a local relational database. As CapStorm’s data recovery features will recover the exact data that is stored in the database, it is always best practice to write unmasked data to the database.

The process of adding a new data transformer is not difficult — all that is required are the following steps:

1. Write a Java class that implements the Capstorm interface com.aslan.sfdc.extract.transform.ITransformer.

2. Add a TransformerRegistry.xml file to CopyStorm’s config directory to register the new transformer with CopyStorm.

3. Update the TableRuleRegistry.xml file to enable the new transformer for the appropriate fields.

The rest of this article describes the development and deployment process for adding a new data transformer to CopyStorm that reverses any input string.

Example: The transformer in this article will turn “Capstorm” into “mrotspaC”.

The following code is an example data transformer implementation. After creating a new data transformer, package it in a jar and store the jar file in the “lib” subdirectory of the CopyStorm installation directory.

CopyStorm discovers available transformers by looking for a file named TransformerRegistry.xml in the CopyStorm config directory.

The following TransformerRegistry.xml will cause CopyStorm to load the “Reverse” transformer:

The Transformer directive adds a new custom transformer using the following attributes:

• name — the friendly name used to apply the transformer to a particular Salesforce field.

• class — the full name of a class that implements the ITransformer interface.

CopyStorm discovers field transformations by looking for FieldTransform elements in the TableRuleRegistryFile.xml configuration file in CopyStorm’s config directory. The general format of this file is described .

The following TableRuleRegistry.xml will cause CopyStorm to reverse the letters in the LastName for all Contacts before writing the LastName to the database:

FieldTransform configuration directives have the following options:

• name — the name of a field in the corresponding Salesforce table.

• transformer — the name of a registered transformer that will be applied to the field data. This is the same as the name specified in Step 2.

If your custom transformer is having no effect on data then the problem is likely in the new XML configuration directives.

To verify that your entry to TableRuleRegistry.xml is correct, use one of the built-in CopyStorm transformers. Built-in transformers include:

• Scramble — replace each letter with a different letter, replace each number with a different number.

• Blank — return a blank string.

If CopyStorm’s built-in transformer works then the problem is either in your TransformerRegistry.xml file or in your code. Put some debugging in your transformer to see if it is ever being called.

• If it is not being called, the problem is in the TransformerRegistry.xml file.

• If it is being called, then fixing the code is up to you!

CopyStorm provides a command line interface to enable CopyStorm to run under Windows Task Scheduler or CRON on Linux. Begin by creating a CopyStorm .

The following example Windows command line will run CopyStorm where:

• CopyStorm is installed in C:\CopyStorm.

• CopyStorm’s configuration file name is DemoCredentials.copyStorm.

• CopyStorm’s configuration file is located in C:\CopyStormDemo.

The Notifications Tab contains parameters used to control who receives a status email when a CopyStorm job runs and under what circumstances email is sent. CopyStorm supports email notifications via SMTP or via a Salesforce SOAP API call.