Help

Re: How to set up automatic external backups for Airtable in 20 minutes

3665 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Nat
6 - Interface Innovator
6 - Interface Innovator

Hi folks,

A few weeks back we shared a show and tell about a custom zapier connector for automating the Airtable environment, and the feedback has been awesome so far. Today, as part of the Openside team, I wanted to share how you can use that connector to solve a long standing missing feature with airtable: external backups. The whole process of setting up automatic backups should take less than 20 minutes.

But first, I want to verify that I’m on the right track. Who here needs/wants their Airtable backed up regularly?

POLL: How critical are backups to your business?

  • Need them daily (it would be bad if I lost even a day’s worth of changes)
  • Need them weekly/monthly (I need it, but this would be sufficient)
  • Not supercritical, I have my own way of backing up data manually
  • Not important, I don’t have any use for an Airtable data backup

0 voters

If you answered “yes” to either option above, or want backups to be set-up-and-automatic ever after, then give this a go:

Here’s what you’ll need to make it work:

Disclaimer: We are not associated with Airtable or Zapier (although we are an Expert Partner of Zapier). Setting up this backup requires entering your API key into Zapier and our connectors which means we technically do have access to it, however we do not store it on our systems anywhere. It is only stored on Zapier’s systems, similar to the existing connector.

Step #1: Schedule by Zapier (trigger)

Using the Schedule by Zapier trigger, set how often you want to run a backup.

L6_stIxz0IM2FSwW9rJc58NMXzOZHpDdEkt_tTN4fQlDAnC-q8KC_YwnpSTgzwPzxofFf6QXYad64hLWBkuAXqOGvccCywElcdED132_inNNaRCgxaPmal0rorMvGdAqYarHghE8.png

Step #2: Add a search action with the Openside Airtable Connector

Select the Openside Airtable Connector. In the “choose action” menu, select the Search: Bulk Find Multiple Records option

D94uLpg40MkjnthIWRBQlGZAW3dMIohxnc9oGs3KR9bW2Tf8xJilVE4o6PZ44rbm33ky_Tctdf4pDaQ0_41LuGq9lRw2LL_QqHThw9IKVd-H1e4iQOzAtboiloFrXkjjog0lKwz_.png

Select your Base, then your first Table to be backed up

90WHgY3oxD81aU6bPUVXJQClia99juH3QHoDz559x639G3DGcasXLVD6s17pzlAYrPSa06puetZ6U6DzyS3ilh2afNkecStJsXhDlXezoUt2ia729iLkAwOW5sbeLYJcxb7Ff9vz.png

(At this point you can choose to customize the backup with a number of options, including filtering by view or formula, sorting the backup by field, or selecting only some fields to be returned. Ignore the Replace Commas option)

Select YES to output as JSON (return results as JSON):

  • JSON is a specialized formatting option that allows the data to transfer smoothly (note: you don’t have to know any JSON to use this option)
  • This will ensure the output data doesn’t have any formatting issues

Step #3: Convert Output to CSV (action)

Add a step to your Zapier workflow. Select the Openside Line Items action option.

Note: If you are OK backing up your data as JSON data instead of a CSV file, this step (and using the Openside Line Items connector) can be skipped. CSV is a more common format and can easily be opened in excel or Google Sheets. JSON is for more technical use cases.

Once you have that, select the option to Convert JSON string to CSV

m2xCjNMoSWKpZSED3Ga2hJwJpVU0fxceXt1jBAGrcruO9HDi6ejkTHk91orLA2wqMetcAL4pGgSrEJDAepzO_WLKjvi8j2kqes-F6D0x584Sh6qAXXzz1NapU4FvQbfpDl-XZ_Mf.png

Under the JSON Text option, use the Insert a Field button in the top right of the field, and select the bulk extract file output from Step #2 above (the result your previous Search in the Zap flow):

u6kRFIQmY1lPsJ2v3ZK-rsldxhiXMHniIJ8yugZ_FCU8tCf2grQvbhVHQ2h8Hmv-Xx3SfZlrd5Cc6phDEzsZEbC-B8tnr1o8gc5vycC8QiaD1Uy2RCwgvXleNnsHwZE7qC-MBKRg.png

In the Output File Name field, specify your file output name. I usually just make it the same name of the table I’m backing up.

This returns a url to the csv file, which can then be attached to an email, input into email body, saved to Google Drive, or viewed later (see Step #4 below)

Repeat Steps #2 and #3 for as many tables are within your base

Depending on your base size, you’ll want to create additional Search and Actions within your Zapier workflow for each table you want backed up. If your base has 3 Tables within it that you want to backup, you’ll want to have a Search (Step #2 above) and Action (Step #3 above) for each: 3 tables x 2 steps for each = 6 steps.

This needs to be done for every table you want backed up

Step #4 Add a final output action

After all table backup steps are set up, you’ll want a final action to specify how the backup files will be output, and where.

A few options available to do this:

Option A:

Send yourself an email via Email by Zapier with all the .csv file links in the body. You can then download or auto-categorize them how you want.

eV7SW-x0GMyePvV4xPKbZH_eczXPLz3uMqMjH6vHNMNEq2pjuuIs5dHoIETK3sVr295NbL6pm-v6jyNscPYjQkqjpR5jHV2MLD8o6_rNKkyzjnj3i1W1vuvaCk0Z10ASlBIZRxff.png

qcEOHtDtN4aYwFQORcWxfNj_ah7yvlhQH8x_aSNN6zUnf4OFcg7AWP9fshzDsTJ-NpDmrBz1pguBltR4aRcwslquNRuzmyqq5PL9ua5p7LR2-GVCUAAOi1rFcDDtB_Dhlqz230JS.png

Note: because of Zapier’s limits to files sizes and number of attachments, it’s not possible at this time to send yourself an email with all the backups attached to it. To accomplish this you’ll need to use an alternative Email solution such as Gmail, Sendgrid, Mailgun, etc. (Option C below)

Option B:

Send it to a Google Docs as a backup location (see Google Docs and Zapier documentation for how to do this)

Option C:

Send it to yourself via your own email service (see Zapier and your email service documentation for setting up this option)

Hope that helps! Would love to hear your feedback on it. If you need more details while setting up your backup, feel free to read the full documentation here.

For convenience, here again are the links to items you need to get this setup:

12 Replies 12
James_Schinnere
4 - Data Explorer
4 - Data Explorer

Does the JSON export include non-text items like images? I am guessing not since like CSV it is also a text, rather than binary, based format. That is the whole problem with the current CSV export, it only includes text based items so it is not a true export or backup. Hopefully Airtable will implement a true export to a common open source format like mysql or sqlite. When I wrote them they say a true backup solution is on the list of requested features.

Jonathan_Green
5 - Automation Enthusiast
5 - Automation Enthusiast

$50 bucks per month is more expensive than airtable itself hahaha

Does the JSON export include non-text items like images? I am guessing not since like CSV it is also a text, rather than binary, based format. That is the whole problem with the current CSV export, it only includes text based items so it is not a true export or backup. Hopefully Airtable will implement a true export to a common open source format like mysql or sqlite. When I wrote them they say a true backup solution is on the list of requested features.

Dan from Openside here. Good question James - the JSON export contains the url of the images, so you’d have to run those through another process to grab the images via the URL. I would suggest something like https://cloudinary.com to perform that. We could explore doing that with you if interested.

Maybe if you only have 1-2 people using airtable :slightly_smiling_face: We work with companies with much more than that. Keep an eye out in future for something that might be more what you’re looking for price wise.

James - thinking more about this, it actually would be just as easy to set up backups for attachments. I’ll do a show and tell on it over next few days. But it essentially involves using the field comparison feature (see here) of our updated trigger to track when an attachment field has changed and when it has to receive a trigger update which then you can get the attachment url and send it to google drive where it will download the attachment and save in your google drive folder.

I think it would be overkill to backup the attachments every day/week/month since most times they wouldn’t change. By just backing up whenever there is a change to the attachments, you use the minimum amount needed.

Tim_Rodgers
4 - Data Explorer
4 - Data Explorer

I see that any linked record fields in the exported CSV do not have the actual linked record name, rather they have a hashed field name.

Usecase: we wanted to restore airtable using the CSV file exports.
Question: how do you suggest we would be able to re-link linked records to another again if we’re unable to read the name of the record?

@Tim_Rodgers,

This is a good question, and backup is a deep concern to any company choosing a SaaS solution. But it’s probably good to disambiguate the term “backup”.

Reality #1

Backing up a complete Airtable solution is far different from backing up the data. A comprehensive solution backup can only be made possible by the SaaS provider unless they expose every aspect of the platform through an open API. Since this is not the case (and likely to never be the case), solution backups are off the table; pun intended. Ideally, Airtable should (and probably will) offer a solution snapshot service (likely for an additional fee) and this seams reasonable.

Reality #2

Protecting the data investment in Airtable by exporting tables separately as CSV files do little to solidify the data relationships because CSV exports are (and always will be) insufficient snapshots of what’s truly stored in the platform. And this is especially the case with binary media, which in turn, is made more complicated if artifacts are captured by reference and stored by value. As such, a true external data snapshot is not easily achieved without significant API development that embraces these Airtable-specific nuances. This realization exposes a deeper concept - data vs content artifacts.

Reality #3

The idea that any quick and dirty backup process could create and manage delta changes over time, is not practical, especially in a platform that offers no event architecture. The right way to create a reliable Time-Machine [like] system is to employ a change management technology the likes of which we see in GitHub and other optimized storage solutions that can instantly detect change and store only differences (ex. StarTeam - I was one of the Sr Architects). To that end, any data snapshotting process should take all of the data periodically and submit it into a process/technology designed to compute and store the deltas. Such delta computation technology must also know how to reverse the process - i.e., reconstitute the current state in full. Lastly, the delta management must also understand change management in the context of binary objects and should probably encrypt the content vault.

The solution and approach offered in this thread is a pretty good stab at [partial] offsite data protection. But we can’t expect it to be a solution snapshot which is impossible to provide unless it comes from Airtable.

Neil_Schneider
5 - Automation Enthusiast
5 - Automation Enthusiast

Pricy and complicated. Great option if budget is not an issue.

Ambroise_Dhenai
8 - Airtable Astronomer
8 - Airtable Astronomer

We just released another way of performing backups. Even though it requires more technical skills because it involves managing an AWS account, it is quite cheap (completely free for most people who just want to perform a daily backup for safety).

Zahir_A
4 - Data Explorer
4 - Data Explorer

Have anyone tried single backup solutions like - probackup.

https://probackup.io/backup/airtable

Found this when I am looking for scheduling options, the requirement is it requires installing their Chrome browser extension. Might take a look into it and explore.

We now have an easier way to create Airtable backups with our On2Air Backups app!

backups main (2400 × 1271 px) (2)

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable
GavinA
8 - Airtable Astronomer
8 - Airtable Astronomer

Here is my solution - hope this helps anyone looking for csv backup.