How to take the backup of a dataset in BigQuery?

Question

We want to create a backup copy of a BigQuery dataset in case a table is accidentally dropped, as it is only recoverable within 7 days.

Is there a way to extend the duration of the recovery period? If not, how can we create a backup of a dataset with a retention period of 30 days in BigQuery?

Answer 1

It is currently not possible to extend the duration of the recovery period. A feature request for the ability to extend the duration of the recovery period has already been created as commented by Katayoon.

Here is a public link to monitor the progress on that issue: https://issuetracker.google.com/120038872

To backup datasets in BigQuery you could either make copies of your dataset, or as a more workable solution, export the data to Cloud Storage, so you can import it back at a later time. Cloud Storage allows you to set a retention period and a lifecycle policy which together will allow you to make sure that data stays undisturbed for the desired amount of time, and that it removes itself after a given time should you wish to save on storage costs.

For how you do export in BigQuery: You can export the tables as AVRO, JSON or CSV files to the Cloud Storage via web UI, command line, an API and using various languages like C#, Go, Python and Java, as long as both are in the same location. There are other limitations to exporting a table, such as file size, Integer encoding, data compression, etc. Link to table export and limitations: https://cloud.google.com/bigquery/docs/exporting-data

You can find the instructions on the procedures here: Retention Policies and Bucket Lock: https://cloud.google.com/storage/docs/using-bucket-lock#lock-bucket Object Lifecycle Management: https://cloud.google.com/storage/docs/managing-lifecycles

Loading data into BigQuery can be done using various file formats, such as CSV, JSON, Avro, Parquet, or ORC and so on. At this moment you can load directly only from local storage, or from Google Storage. More on loading data, file formats, data sources and limitations by following the link: https://cloud.google.com/bigquery/docs/loading-data

More information on Exporting tables: https://cloud.google.com/bigquery/docs/exporting-data Export limitations: https://cloud.google.com/bigquery/docs/exporting-data#export_limitations Loading data into BigQuery: https://cloud.google.com/bigquery/docs/loading-data Wildcards: https://cloud.google.com/storage/docs/gsutil/addlhelp/WildcardNames Merging the file: https://cloud.google.com/storage/docs/gsutil/commands/compose