How to restore a deleted table in BigQuery, and back up your data

How long you have

The numbers are from Google’s time travel and fail-safe page, read in October 2026. Fail-safe data cannot be queried or restored by you; you open a support case. So the practical deadline is the time travel window, and the first thing to check after an accident is how long that window is on the dataset.

Restore a deleted table

You cannot query a deleted table, even with a time decorator. You restore it by copying, as Google’s restore deleted tables page describes.

The decorator after the table name takes three forms:

Pick a moment just before the deletion. If you do not know when it happened, look the job up in the BigQuery job history: the delete is a job with a start time.

To get milliseconds for a given time, ask BigQuery:

SELECT UNIX_MILLIS(TIMESTAMP '2026-10-07 09:00:00+00');
-- 1791363600000

Then:

bq cp mydataset.mytable@1791363600000 mydataset.mytable_restored

Restore into a new name first, check the rows, and only then rename or copy over.

If a table with the same name was created after the deletion, the name now points at the new table. The old one is still reachable: use a timestamp from when the original existed.

The role you need: BigQuery User on the project is enough.

Restore a deleted dataset

A whole dataset comes back with one statement, inside the same time travel window:

UNDROP SCHEMA `my_dataset`;

From Google’s restore deleted datasets page:

• Tables, views, routines, dataset properties and security settings come back.
• It does not work if a dataset with the same name already exists. If you created a replacement, the original cannot be restored.
• Materialized views need a manual refresh afterwards, and authorized views, datasets and routines need to be authorized again.
• A dataset that has been undeleted cannot be deleted again for seven days.

Undo a bad UPDATE or DELETE

More often the table is still there and the rows are wrong: an UPDATE without a WHERE, a load that ran twice. Time travel reads the table as it was:

SELECT COUNT(*) AS row_count,
       MAX(data_date) AS latest_day
FROM mydataset.mytable
  FOR SYSTEM_TIME AS OF
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)
WHERE data_date >= '2026-10-01';

On a daily-loaded table in our project this returned 50,541 rows with a latest day of 3 October. The same query without the FOR SYSTEM_TIME clause returned 69,319 rows and 4 October. The difference is the load that arrived in between.

To put the old state back, write it to a new table and swap:

CREATE TABLE mydataset.mytable_fixed AS
SELECT *
FROM mydataset.mytable
  FOR SYSTEM_TIME AS OF TIMESTAMP '2026-10-05 08:00:00+00';

This statement writes a table, so it was not executed for this guide; it follows Google’s reference.

Two errors we did hit while testing:

• Asking for a moment outside the window. Eight days back returned Invalid time travel timestamp … Cannot read before …. The message tells you the earliest moment available.
• Comparing then and now in one query. A query that reads the same table with and without FOR SYSTEM_TIME AS OF fails: every reference to a table must use the same timestamp. Run two queries, or save the old state to a table first.

More patterns are in the time travel guide and the time travel examples.

What cannot be recovered

From the same Google pages:

• A single partition. Only whole tables are restored. To get one day back, read it with FOR SYSTEM_TIME AS OF and insert it.
• Views and materialized views. Recreate them from their SQL, which is one more reason to keep that SQL in version control.
• External tables. The data lives outside BigQuery; time travel does not cover it.
• Table metadata. Time travel keeps data, not descriptions or labels.
• Anything older than time travel plus fail-safe.

BigQuery has no backups unless you make them

Time travel is an undo button, not a backup. Seven days is short: a mistake in a monthly job is found after the window has closed. For anything longer, the tool is a table snapshot.

A snapshot is a read-only copy of a table at a point in time. From Google’s introduction to table snapshots:

• It costs nothing at first. You pay only for data that later changes or is deleted in the base table, since the snapshot must then keep its own copy.
• It can have an expiration, after which it deletes itself.
• It must be in the same region as the base table.
• Views, materialized views and external tables cannot be snapshotted.

Create one, and restore from it:

CREATE SNAPSHOT TABLE backups.orders_20261007
CLONE mydataset.orders
OPTIONS (
  expiration_timestamp = TIMESTAMP '2027-01-07 00:00:00+00'
);

CREATE TABLE mydataset.orders_restored
CLONE backups.orders_20261007;

Neither statement was executed for this guide; both follow Google’s DDL reference.

A simple backup routine

• Snapshot the tables you cannot rebuild, on a schedule, into a separate dataset. Raw exports that the source no longer holds come first.
• Set an expiration so snapshots do not pile up: for example 35 days for daily ones and a year for monthly ones.
• For a copy outside BigQuery, export to Cloud Storage. The formats are in how to export BigQuery data.
• Keep the time travel window at seven days unless storage billing gives you a reason to shorten it. On physical storage billing, time travel and fail-safe bytes are charged; on logical billing they are not. See BigQuery pricing.

Recover the logic, not only the rows

A restored table is half of the recovery. The other half is knowing which query built it and which reports read it, and that is usually what is missing when a table disappears.

That is the case for keeping report logic outside any one person’s saved queries. In OWOX Data Marts each Data Mart is a named, described definition, a table, a view or SQL, with its runs listed in Run History. When a source table is restored, the definitions that read it are still there to re-run, and Run History shows which runs failed while it was missing. OWOX Data Marts does not back up or transform your data; it keeps the definitions and the schedule in one place, in your own warehouse.

Google BigQueryComprehensive Guide to BigQuery Aggregate Functions for 2025April 18, 2024→Google BigQueryBigQuery array functions: syntax and tested examplesOctober 6, 2026→Google BigQueryBigQuery CAST and SAFE_CAST: convert data types without errorsOctober 6, 2026→

See all articles →