How to RESTORE TABLE from Backup in BigQuery

What does RESTORE TABLE do?

RESTORE TABLE instantly rebuilds a table from a snapshot or time-travel backup without exporting or re-loading data.The command is ideal for accidental deletes, schema mishaps, or data corruption.

When should I use RESTORE TABLE?

Use it when a production table (e.g., Orders) was dropped or overwritten and you need the exact state as of a previous point-in-time or an existing snapshot table.

How do I reference a snapshot table?

BigQuery auto-creates daily snapshots if you enabled backups, or you can create manual snapshots with CREATE SNAPSHOT TABLE. RESTORE TABLE references that snapshot.

Can I restore by timestamp?

Yes.The FOR SYSTEM_TIME AS OF clause lets you pick any moment within the 7-day time-travel window.

Does RESTORE overwrite existing tables?

Add REPLACE to overwrite, or use IF NOT EXISTS to avoid accidental replacement.Omit both to raise an error if the target exists.

Best practices for production restores

1) Always restore to a new table first for validation, e.g., Orders_restored.
2) Verify row counts and key metrics before swapping.
3) Automate snapshot creation via scheduled queries.

Example: rescue a dropped Orders table

Suppose Orders was dropped at 09:05.Create a fresh copy using the 09:00 backup, validate, then replace.

Step 1 – Restore to a temp table

RESTORE TABLE mydb.Orders_temp
FROM SNAPSHOT TABLE mydb.backups.Orders_20230910_0900;

Step 2 – Run sanity checks

SELECT COUNT(*) AS rows, SUM(total_amount) AS revenue
FROM mydb.Orders_temp;

Step 3 – Replace the live table

RESTORE TABLE mydb.Orders
FROM SNAPSHOT TABLE mydb.backups.Orders_20230910_0900
REPLACE;

Key takeaways

RESTORE TABLE is the fastest, safest way to recover BigQuery data. Practice restores on non-critical datasets to build muscle memory.

.