FAQs: SQL Database Exports

Apricot SQL database exports provide your organization with direct access to your raw Apricot data in SQL format. These exports are designed for advanced users who want to run external analytics, build custom reports, or store a snapshot of their data outside the Apricot platform.

This article outlines what SQL exports are, how they work, and what your team needs to manage them successfully.

A database export is a downloadable .sql file containing raw MySQL dumps of all the data from your live production Apricot database. Any data that exists in the database at the time of the export is automatically included in the export; they cannot contain only part of the data or database.

The user receiving the export must have a MySQL server up and running in order to access the data, as the exports cannot be "opened" or "run" in MySQL Workbench. Once you receive the file, you'll download the data from the SFTP server and restore your database onto your own MySQL server. Exports cannot be restored in Microsoft SQL and can only be restored to a MySQL server. Once this is done, MySQL Workbench can be used to run queries against your database running on the MySQL server.

You do not need a license to use MySQL, as it is covered under the GNU General Public License. You can learn more about MySQL here. MySQL Community Edition can be downloaded here.

Apricot offers two types of SQL exports:

Important: We do not have the ability to customize the export process for either export type. This means we cannot include attached or imported files to any recurring export for any organization, and we cannot prevent previous files from being overwritten each night. It is also not possible to request a specific naming convention for any database exports.

It takes about two weeks to configure the initial setup of the SFTP server then complete an export. This time can vary depending on a number of factors, including the amount and type of data that exists in the database.

Important: It is not possible to request a specific time for the export to be delivered. The file will be delivered daily or weekly based on the requested frequency by the start of business hours. Please plan accordingly if you are working with tight reporting or project deadlines!

The backup files are zipped and uploaded to an Amazon AWS S3 bucket.

When either export type is complete, a Word file will be provided that contains all the information needed to connect to the S3 bucket and download your export. This Word file will include:

Both export types can only be provided to Bonterra-managed SFTP servers. Organizations are welcome to automate the process of downloading the files from our SFTP servers and uploading them to their own SFTP servers or database servers.

Important: One-time exports are only available for 30 days after the export is complete. After 30 days, the export is automatically deleted. We highly recommend attempting to connect to the S3 bucket as soon as possible to allow for troubleshooting your connection before the file is deleted.

Both one-time exports and recurring exports are paid offerings that can be acquired by engaging the Account Management team here.

While both export types are only delivered in SQL format, the Professional Services team can pull CSV files from reports once the project is re-scoped through the Account Management team. If you require only a partial export, this would also be scoped out and performed by the Professional Services team.

You're free to use any SFTP client you choose to access your file (though we strongly recommend against WinSCP due to its known issues with connecting to AWS S3 buckets). For recurring backups, we recommend using CyberDuck, a free and open-source FTP/SFTP solution for accessing your files.

Note: Some approaches to downloading CyberDuck (such as through the Windows Store or Mac App Store) come with a registration key that disables a donation prompt. While you may choose to purchase a registration key to support the development of CyberDuck, the registration key is not required to use the software for transfer files. If you are unable to bypass this registration key to find your backups, navigate to the Menu options in CyberDuck, then click Bookmark > History > Find AmazonS3.

Once you download the zipped file, you'll need to unzip it. This process may vary depending on which application you use. You'll then be prompted for the zip password (provided via Keeper).

The Keeper links provided in the Word file are only accessible once and for one week before they expire. We highly recommend opening the links as soon as possible and copying the AWS secret access key and zip password to a secure location. If they expire, Bonterra can recover the zip password for a recurring export, but a one-time export will likely need to be redone as those zip passwords aren't stored anywhere after the export is complete.

Clicking on a Keeper link will take you to a page similar to the following image:

A description of the record being shared is found at the top. Clicking the eye icon to the right will reveal the hidden record.

Notes:

The name of the file in your S3 bucket will be 'yourorganizationID.zip'. (ex. '5828.zip'). The name of the unzipped folder will be your organization ID as well. You can follow this folder path to get to your data: 'yourorganizationID' > var > tmp > 'SAASOPS-ticketnumber'.

Here, you'll see a SQL file (ex. 'apricot_5828.sql') which is your database, and the folder named after your organization ID will contain all of your attached and imported files.

The name of the file in your S3 bucket will be a generic 'databasebackup.zip'. The name of the unzipped folder will be 'databasebackup' with only a single file inside called 'database.sql'.

Bonterra can assist with providing SFTP credentials to access the file in the S3 bucket and troubleshooting credentials. However, we cannot assist with downloading your file or extracting the data from the received SQL file. We also do not provide guidance on attempting to convert data to any version of SQL aside from MySQL 8.

Any further steps or automation processes your organization would like to implement with your data should be managed internally with your IT team. Additional troubleshooting information can be found in the following FAQs.

Users will only have permissions for their assigned folder in the AWS S3 bucket. This error typically occurs when users don't include their specific folder name in the connection path (ex. 'SAASOPS-ticketnumber' for one-time exports).

This is a rare situation that typically occurs when the user has intermittent internet issues. Since the download is coming directly from AWS, Bonterra's only course of action after checking your connection is to process the export again and break the .zip file into multiple smaller pieces.

If there's a referenced table that isn't present in the data (ex. 'magician'), users should "comment out" that section of the restore file. Beyond this, we recommend consulting with the Professional Services team here if there's a need for further SQL support.

Bonterra does not generate customer-specific ERDs or data maps. There is a table called “forms_metadata_view” in the SQL backup which has your fields, sections, and forms mapping. You may restore the entire database then query this specific table for these details, or you may restore just this table if you only want a mapping document.

For more information on performing a restore, please review MySQL's process documentation here.

Each Tier 2 record will have a Parent ID value in the Documents table that matches the Document ID of a Tier 1 record. This allows you to confirm that the association or relationship between records is honored in your queries.

This can be achieved by referencing the Documents table and the column named "Active". If the value in this column is "0", that means it is an archived record. An example of simple SQL that may help search for active records is below:
​
SELECT * from documents where id = $record_id AND active =1;