FAQs | SQL Database Exports

This living document provides answers for common questions regarding recurring and one-time Apricot database exports.

Updated over a week ago

Q: What are our options for exporting our database?

Options include one-time or recurring backups of your SQL database exported in SQL format for collection from Workdocs or an SFTP server, respectively. These are paid offerings that can be acquired by engaging your Account Management team at accountmanagement@bonterratech.com

Full backups of your database are sent at a contract-specified recurrence (typically monthly) to an SFTP server. You may then download all of your data with provisioned access to the SFTP server, restore your database onto your own servers, then query your database using SQL.

We also provide differentials, or delta files, at a contract-specified recurrence (ex. nightly, weekly, biweekly) that contain the changes made in your database since the last full backup. For clients with data warehouses, leveraging automated jobs that push both these full and differential backups into your data warehouses ensures a streamlined and resource-efficient process for maintaining up-to-date information.

Q: How long will it take to prepare a backup?

It takes about two weeks for the initial setup of the SFTP and nightly export.

Q: What file type will the backup be?

The file is provided in a SQL file format.

Q: Can we receive the backup in a non-SQL format?

Yes, we can do a .csv file from reports, but it would need to be re-scoped and include SC hours.

Q: Can we pick the time that the backup is run?

No, this cannot be requested as we use automatic backups already in place that vary significantly on time of day. Nightly backups are common, and customers may setup their own processes to access the data at midnight to ensure the data is always available.

Q: How do I access the file(s)?

One-time backups are accessed through Amazon WorkDocs.

For recurring backups, our Professional Services team recommends using CyberDuck, a free and open source FTP/SFTP solution for accessing your files. This has been tested and verified to ensure it will work for accessing your files.

Note: Apricot Support is unable to access your files directly for you. We recommend performing any initial testing with CyberDuck prior to using your organization's own FTP/SFTP software. This will help us troubleshoot where access issues may lie.

Q: CyberDuck is requesting a registration key. What do I do?

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, please try the following steps:

In CyberDuck, navigate to the Menu options > Bookmark > History > Find AmazonS3.

Q: Do I need a MySQL/Microsoft SQL License to access my data?

Apricot databases and database backups will require an installation of MySQL in order to access them. 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.

Q: The password provided with the backup wasn’t needed to access the data. What is it for?

The password provided is only needed to unzip the files. It is not needed to access MySQL, as you will need to set up your own password.

Q: I lost the password provided via PrivNote/SafeNote. Can you resend it?

Yes, we can get this password from our CloudOps team for you.

Q: Where are my attachments?

Attachments are available at no cost for Apricot. Attachments are labeled as the name of the document. You will need to download all contents of the folder in Amazon WorkDocs (not just the .zip) for attachments.

Q: I'm unable to download the .zip file on my Mac. What do I do?

You may need to download another app to access zipped files on a Mac. On MacOS, we recommend using The Unarchiver, a free file archiving tool.

Q: Can you help me extract my data from the SQL file?

We can troubleshoot actually accessing the file itself from our AWS. However, we cannot assist with downloading or using MSSQL, MySQL, or any other tools used to extract the data from the received SQL file.

Q: May I have some kind of mapping document or data dictionary?

There is a table called “forms_metadata_view” in the SQL backup which has your fields/sections/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.

Q: How can I view the relationships between my Tier 1 and Tier 2 records in these tables?

This can be achieved by referencing the Documents table. Each Tier 2 record will have a Parent ID value in this 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.

Q: Is there a way to filter our SQL export to only display active forms and records?

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;

Q: How long do you keep database backups for?

We store database backups for 13 months after they're created as a safeguard for our clients.

Did this answer your question?