Restore deleted tables

This document describes how to restore (or undelete ) a deleted table in BigQuery. You can restore a deleted table within the time travel window specified for the dataset, including explicit deletions and implicit deletions due to table expiration. You can also configure the time travel window .

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

The time travel window can have a duration between two and seven days. After the time travel window has passed, BigQuery provides a fail-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 a table using any method, including opening a support ticket.

Before you begin

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

Required roles

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

You might also be able to get the required permissions through custom roles or other predefined roles .

Restore a table

When you restore a table from historical data, tags from the source table aren't copied to the destination table.

You can restore a table that was deleted but is still within the time travel window 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 must restore it first.

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

Console

You can't undelete a table by using the Google Cloud console.

bq

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
To restore a table, first determine a UNIX timestamp of when the table existed (in milliseconds). You can use the Linux date command to generate the Unix timestamp from a regular timestamp value:
```
date -d '2023-08-04 16:00:34.456789Z' +%s000
```
Then, use the bq copy command with the @<time> time travel decorator to perform the table copy operation.

For example, enter the following command to copy the mydataset.mytable table at the time 1418864998000 into a new table mydataset.newtable .
```
bq cp mydataset.mytable@1418864998000 mydataset.newtable
```
(Optional) Supply the --location flag and set the value to your location .

You can also specify a relative offset. The following example copies the version of a table from one hour ago:
```
bq cp mydataset.mytable@-3600000 mydataset.newtable
```
For more information, see Restore a table from a point in time .

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Go API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

  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. 
 func 
  
 deleteAndUndeleteTable 
 ( 
 projectID 
 , 
  
 datasetID 
 , 
  
 tableID 
  
 string 
 ) 
  
 error 
  
 { 
  
 // projectID := "my-project-id" 
  
 // datasetID := "mydataset" 
  
 // tableID := "mytable" 
  
 ctx 
  
 := 
  
 context 
 . 
 Background 
 () 
  
 client 
 , 
  
 err 
  
 := 
  
 bigquery 
 . 
 NewClient 
 ( 
 ctx 
 , 
  
 projectID 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "bigquery.NewClient: %v" 
 , 
  
 err 
 ) 
  
 } 
  
 defer 
  
 client 
 . 
 Close 
 () 
  
 ds 
  
 := 
  
 client 
 . 
 Dataset 
 ( 
 datasetID 
 ) 
  
 if 
  
 _ 
 , 
  
 err 
  
 := 
  
 ds 
 . 
 Table 
 ( 
 tableID 
 ). 
 Metadata 
 ( 
 ctx 
 ); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 // Record the current time.  We'll use this as the snapshot time 
  
 // for recovering the table. 
  
 snapTime 
  
 := 
  
 time 
 . 
 Now 
 () 
  
 // "Accidentally" delete the table. 
  
 if 
  
 err 
  
 := 
  
 client 
 . 
 Dataset 
 ( 
 datasetID 
 ). 
 Table 
 ( 
 tableID 
 ). 
 Delete 
 ( 
 ctx 
 ); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 // 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 
 . 
  WriteTruncate 
 
  
 job 
 , 
  
 err 
  
 := 
  
 copier 
 . 
 Run 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 status 
 , 
  
 err 
  
 := 
  
 job 
 . 
 Wait 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 if 
  
 err 
  
 := 
  
 status 
 . 
  Err 
 
 (); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 ds 
 . 
 Table 
 ( 
 recoverTableID 
 ). 
 Delete 
 ( 
 ctx 
 ) 
  
 return 
  
 nil 
 }

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Java API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

  import 
  
 com.google.cloud.bigquery. BigQuery 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryException 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. CopyJobConfiguration 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Job 
 
 ; 
 import 
  
 com.google.cloud.bigquery. JobInfo 
 
 ; 
 import 
  
 com.google.cloud.bigquery. TableId 
 
 ; 
 // Sample to undeleting a table 
 public 
  
 class 
 UndeleteTable 
  
 { 
  
 public 
  
 static 
  
 void 
  
 runUndeleteTable 
 () 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 datasetName 
  
 = 
  
 "MY_DATASET_NAME" 
 ; 
  
 String 
  
 tableName 
  
 = 
  
 "MY_TABLE_TABLE" 
 ; 
  
 String 
  
 recoverTableName 
  
 = 
  
 "MY_RECOVER_TABLE_TABLE" 
 ; 
  
 undeleteTable 
 ( 
 datasetName 
 , 
  
 tableName 
 , 
  
 recoverTableName 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 undeleteTable 
 ( 
 String 
  
 datasetName 
 , 
  
 String 
  
 tableName 
 , 
  
 String 
  
 recoverTableName 
 ) 
  
 { 
  
 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. 
  
  BigQuery 
 
  
 bigquery 
  
 = 
  
  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. 
  
 long 
  
 snapTime 
  
 = 
  
 System 
 . 
 currentTimeMillis 
 (); 
  
 // Construct the restore-from tableID using a snapshot decorator. 
  
 String 
  
 snapshotTableId 
  
 = 
  
 String 
 . 
 format 
 ( 
 "%s@%d" 
 , 
  
 tableName 
 , 
  
 snapTime 
 ); 
  
 // Construct and run a copy job. 
  
  CopyJobConfiguration 
 
  
 configuration 
  
 = 
  
  CopyJobConfiguration 
 
 . 
 newBuilder 
 ( 
  
 // Choose a new table ID for the recovered table data. 
  
  TableId 
 
 . 
 of 
 ( 
 datasetName 
 , 
  
 recoverTableName 
 ), 
  
  TableId 
 
 . 
 of 
 ( 
 datasetName 
 , 
  
 snapshotTableId 
 )) 
  
 . 
 build 
 (); 
  
  Job 
 
  
 job 
  
 = 
  
 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 
 
  
 | 
  
 InterruptedException 
  
 e 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Table not found. \n" 
  
 + 
  
 e 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 }

// Import the Google Cloud client library const { BigQuery } = require ( ' @google-cloud/bigquery ' ); const bigquery = new BigQuery (); async function undeleteTable () { // 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. */ const snapshotEpoch = Date . now (); // Delete the table await bigquery . dataset ( datasetId ) . table ( tableId ) . delete (); console . log ( `Table ${ tableId } deleted.` ); // Construct the restore-from table ID using a snapshot decorator. const snapshotTableId = ` ${ tableId } @ ${ snapshotEpoch } ` ; // Construct and run a copy job. await bigquery . dataset ( datasetId ) . table ( snapshotTableId ) . copy ( bigquery . dataset ( datasetId ). table ( recoveredTableId )); console . log ( `Copied data from deleted table ${ tableId } to ${ recoveredTableId } ` ); }

import time from google.cloud import bigquery # 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 ) )

What's next

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License , and code samples are licensed under the Apache 2.0 License . For details, see the Google Developers Site Policies . Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-10-24 UTC.