Recover deleted table in Google BigQuery

Does your BigQuery table got deleted accidentally or got expired and you forgot to take a backup, you don’t have to worry about that anymore, we got that covered for you here in this post. But this recovery can be done only within the 48 hours of deletion. Google keeps the snapshot of BigQuery tables only for 2 days.

Recovery contains two steps:

  1. Copy snapshot of the table to a temporary table.
  2. Then you can copy temporary table back to the original location.

First, get the UNIX timestamp from a time when the table was live

⋊> date +%s

(Multiply the timestamp by 1000 since we need millisecond timestamp)

Run the following command to copy snapshot to temporary table,

⋊> bq cp dataset_name.table_name@1498137851000 dataset_name.temp_table
Waiting on bqjob_s4d8d7dfse2e4dae73_0000014a5af453a043_1 ... (0s)
Current status: DONE
Tables 'dataset_name.table_name@1498137851000' successfully copied to

Now, copy the temporary table back to original table,

⋊> bq cp dataset_name.temp_table dataset_name.table_name
Waiting on bqjob_s4d8d7dfse2e4dae73_0000014dfdfsaf453a0sd43_1 ... (0s)
Current status: DONE
Tables 'dataset_name.temp_table' successfully copied to

That’s it, you have your back where it was.