Restore deleted tables

This document describes how to restore (orundelete) a deleted table inBigQuery.You can restore a deleted table within the time travel window specified for thedataset, including explicit deletions and implicit deletions due to tableexpiration. You can alsoconfigure the time travel window.

For information about how to restore an entire deleted dataset or snapshot,see the following resources:

• Restore deleted datasets
• Restore table snapshots

The time travel window can have a duration between two and seven days. After thetime travel window has passed, BigQuery provides afail-safe period where the deleted data is automatically retained for an additional seven days.Once the fail-safe period has passed, it isn't possible to restore atable using any method, including opening a support ticket.

Before you begin

Ensure that you have the necessary Identity and Access Management (IAM) permissions torestore a deleted table.

Required roles

To get the permissions that you need to restore a deleted table, ask your administrator to grant you theBigQuery User (roles/bigquery.user) IAM role on the project. For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Restore a table

You can restore a table from historical data by copying the historical data intoa new table. Copying historical data works even if the table was deleted or hasexpired, as long as you restore the table within the duration of the time travelwindow.

When you restore a table from historical data,tags from the source table aren't copied to the destination table.Table partitioning information also isn't copied to the destination table. Torecreate the partitioning scheme of the original table, you can view the initialtable creation request inCloud Logging and use that information to partition the restored table.

You can restore a table that was deleted but is still within the time travelwindow by copying the table to a new table, using the@<time> time decorator.You can't query a deleted table, even if you use a time decorator. You mustrestore it first.

Use the following syntax with the@<time> time decorator:

• tableid@TIME whereTIME isthe number of milliseconds since the Unix epoch.
• tableid@-TIME_OFFSET whereTIME_OFFSET is the relative offset from the currenttime, in milliseconds.
• tableid@0: Specifies the oldest available historical data.

To restore a table, select one of the following options:

import("context""fmt""time""cloud.google.com/go/bigquery")// deleteAndUndeleteTable demonstrates how to recover a deleted table by copying it from a point in time// that predates the deletion event.funcdeleteAndUndeleteTable(projectID,datasetID,tableIDstring)error{// projectID := "my-project-id"// datasetID := "mydataset"// tableID := "mytable"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()ds:=client.Dataset(datasetID)if_,err:=ds.Table(tableID).Metadata(ctx);err!=nil{returnerr}// Record the current time.  We'll use this as the snapshot time// for recovering the table.snapTime:=time.Now()// "Accidentally" delete the table.iferr:=client.Dataset(datasetID).Table(tableID).Delete(ctx);err!=nil{returnerr}// Construct the restore-from tableID using a snapshot decorator.snapshotTableID:=fmt.Sprintf("%s@%d",tableID,snapTime.UnixNano()/1e6)// Choose a new table ID for the recovered table data.recoverTableID:=fmt.Sprintf("%s_recovered",tableID)// Construct and run a copy job.copier:=ds.Table(recoverTableID).CopierFrom(ds.Table(snapshotTableID))copier.WriteDisposition=bigquery.WriteTruncatejob,err:=copier.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}ds.Table(recoverTableID).Delete(ctx)returnnil}

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.CopyJobConfiguration;importcom.google.cloud.bigquery.Job;importcom.google.cloud.bigquery.JobInfo;importcom.google.cloud.bigquery.TableId;// Sample to undeleting a tablepublicclassUndeleteTable{publicstaticvoidrunUndeleteTable(){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_TABLE";StringrecoverTableName="MY_RECOVER_TABLE_TABLE";undeleteTable(datasetName,tableName,recoverTableName);}publicstaticvoidundeleteTable(StringdatasetName,StringtableName,StringrecoverTableName){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();// "Accidentally" delete the table.bigquery.delete(TableId.of(datasetName,tableName));// Record the current time.  We'll use this as the snapshot time// for recovering the table.longsnapTime=System.currentTimeMillis();// Construct the restore-from tableID using a snapshot decorator.StringsnapshotTableId=String.format("%s@%d",tableName,snapTime);// Construct and run a copy job.CopyJobConfigurationconfiguration=CopyJobConfiguration.newBuilder(// Choose a new table ID for the recovered table data.TableId.of(datasetName,recoverTableName),TableId.of(datasetName,snapshotTableId)).build();Jobjob=bigquery.create(JobInfo.of(configuration));job=job.waitFor();if(job.isDone() &&job.getStatus().getError()==null){System.out.println("Undelete table recovered successfully.");}else{System.out.println("BigQuery was unable to copy the table due to an error: \n"+job.getStatus().getError());return;}}catch(BigQueryException|InterruptedExceptione){System.out.println("Table not found. \n"+e.toString());}}}

// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionundeleteTable(){// Undeletes "my_table_to_undelete" from "my_dataset"./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = "my_dataset";// const tableId = "my_table_to_undelete";// const recoveredTableId = "my_recovered_table";/**   * TODO(developer): Choose an appropriate snapshot point as epoch milliseconds.   * For this example, we choose the current time as we're about to delete the   * table immediately afterwards.   */constsnapshotEpoch=Date.now();// Delete the tableawaitbigquery.dataset(datasetId).table(tableId).delete();console.log(`Table${tableId} deleted.`);// Construct the restore-from table ID using a snapshot decorator.constsnapshotTableId=`${tableId}@${snapshotEpoch}`;// Construct and run a copy job.awaitbigquery.dataset(datasetId).table(snapshotTableId).copy(bigquery.dataset(datasetId).table(recoveredTableId));console.log(`Copied data from deleted table${tableId} to${recoveredTableId}`);}

importtimefromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Choose a table to recover.# table_id = "your-project.your_dataset.your_table"# TODO(developer): Choose a new table ID for the recovered table data.# recovered_table_id = "your-project.your_dataset.your_table_recovered"# TODO(developer): Choose an appropriate snapshot point as epoch# milliseconds. For this example, we choose the current time as we're about# to delete the table immediately afterwards.snapshot_epoch=int(time.time()*1000)# ...# "Accidentally" delete the table.client.delete_table(table_id)# Make an API request.# Construct the restore-from table ID using a snapshot decorator.snapshot_table_id="{}@{}".format(table_id,snapshot_epoch)# Construct and run a copy job.job=client.copy_table(snapshot_table_id,recovered_table_id,# Must match the source and destination tables location.location="US",)# Make an API request.job.result()# Wait for the job to complete.print("Copied data from deleted table{} to{}".format(table_id,recovered_table_id))

If you anticipate that you might want to restore a table later than what isallowed by the time travel window, then create a table snapshot of the table.For more information, seeIntroduction to table snapshots.

You cannot restore a logical view directly. For more information, seeRestore aview.

Identify the cause of table deletion

You can use theINFORMATION_SCHEMA.TABLE_STORAGEview to determine how a table was deleted.

TheINFORMATION_SCHEMA.TABLE_STORAGE view contains information about currenttables and tables deleted within the time travel window. If a table was deleted,thetable_deletion_time column contains the deletion timestamp, and thetable_deletion_reason column contains the deletion method.

To determine the reason a table was deleted, query theINFORMATION_SCHEMA.TABLE_STORAGE view:

SELECTtable_name,deleted,table_deletion_time,table_deletion_reasonFROM`PROJECT_ID`.`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGEWHEREtable_schema="DATASET_ID"ANDtable_name="TABLE_ID"

Replace the following variables:

• PROJECT_ID: your project ID.
• REGION: the region of the dataset that contained the table.
• DATASET_ID: the ID of the dataset that contained the table.
• TABLE_ID: the ID of the deleted table.

Thetable_deletion_reason column explains why the table was deleted:

• TABLE_EXPIRATION: The table was deleted after the set expiration time.
• DATASET_DELETION: The dataset the table belonged to was deleted by a user.
• USER_DELETED: The table was deleted by a user.

Troubleshoot table recovery

Querying the deleted table using a timestamp in the past

You cannot restore table data by querying a deleted table in the past using atimestamp decorator or by usingFOR SYSTEM_TIME AS OFto save the result in a destination table. Using either of these methodsgenerates the following error:

Not found: Table myproject:mydataset.table was not found in locationLOCATION

Instead, to copy the table, follow the steps inRestore a table.

Error:VPC Service Controls: Request is prohibited by organization's policy

When you attempt to run the copy command from Google Cloud Shell you mayencounter an error like the following:

BigQuery error in cp operation: VPC Service Controls: Request is prohibited by organization's policy

Using Cloud Shell from the Google Cloud console with VPC SC isnot supported,because it gets treated as a request outside of the service perimeters andaccess to data that VPC Service Controls protects is denied. To work around this issue,launch andconnect to Cloud Shell locally with the Google Cloud CLI.

Error:Latest categories are incompatible with schema

If you run the copy command from Google Cloud Shell, you may receive an errorlike the following:

Latest categories are incompatible with schema atTIMESTAMP

There are several possible causes for this error:

• The destination table schema is different from the schema of the originaltable (extra columns are allowed as long as they don't have anycolumn-level policy tags attached).
• The destination tablecolumn-level policy tagsare configured differently from the source table.

To resolve this error:

1. Ensure that the schema of the destination table is identical, and ensure thatnone of the columns in the original table are missing from the destinationtable.
2. Remove any column-level policy tags from the destination tablethat aren't in the original table's schema.

Error:BigQuery error in cp operation: Invalid time travel timestamp

If you run thebq copy command from Google Cloud Shell, you may receive anerror like the following:

BigQuery error in cp operation: Invalid time travel timestamp 1744343690000 fortable PROJECT_ID:DATASET_ID.TABLE_ID@1744343690000.Cannot read before 1744843691075

This error indicates that you are trying to recover data from the table stateprior to the time travel window or before the table was created. This isnot supported. The error message contains the latest timestamp that can beused to read the table data. Use the timestamp in the error in thebq copycommand.

This error can also occur when you provide a negative timestamp value, forexample,TABLE@-1744963620000. Instead, use a time-offset that can be usedwith the- sign.

BigQuery error in cp operation: Invalid time travel timestamp 584878816 fortable PROJECT_ID:DATASET_ID.TABLE_ID@584878816.Cannot read before 1744843691075

This error message indicates that thebq cp command contains a negativetimestamp value as an offset, and that you attempted to read the table atCURRENT_TIMESTAMP - PROVIDED TIMESTAMP. This value is normally a timestampin 1970. To work around this issue, verify the offset or timestamp values whenyou set the table decorator value and use the- sign appropriately.

Materialized views

You can't restore a deleted materialized view directly. If you delete amaterialized view, you mustrecreate it.

If you delete a table that is a base table for a materialized view,the materialized view can no longer be queried or refreshed. If you restorethe base table by following the steps inRestore a table,you must alsorecreateany materialized views that use that table.

What's next

• Learn how tocreate and use tables.
• Learn how tomanage tables.
• Learn how tomodify table schemas.
• Learn aboutworking with table data.