Restore table snapshots
This document describes how to create a writeable table from atable snapshot by using the Google Cloud console, aCREATE TABLE CLONE query, abq cp command, or thejobs.insert API.It is intended for users who are familiar withtable snapshots.
Permissions and roles
This section describes theIdentity and Access Management (IAM) permissionsthat you need to create a writeable table from a table snapshot, and thepredefined IAM rolesthat grant those permissions.
Permissions
To create a writeable table from a table snapshot, you need the followingpermissions:
| Permission | Resource |
|---|---|
All of the following:bigquery.tables.getbigquery.tables.getDatabigquery.tables.restoreSnapshot | The table snapshot that you want to copy into a writeable table. |
bigquery.tables.create | The dataset that contains the destination table. |
Roles
The predefined BigQuery roles that provide the requiredpermissions are as follows:
| Role | Resource |
|---|---|
Any of the following:bigquery.dataEditorbigquery.dataOwnerbigquery.admin | The table snapshot that you want to copy into a writeable table. |
Any of the following:bigquery.dataEditorbigquery.dataOwnerbigquery.admin | The dataset that contains the destination table. |
Restore a table snapshot
To create a writeable table from a snapshot, specify the table snapshot that youwant to copy and the destination table. The destination table can be a newtable, or you can overwrite an existing table with the table snapshot.
Restore to a new table
You can restore a table snapshot into a new table by using one of the following options:
Console
In the Google Cloud console, go to theBigQuery page.
In the left pane, clickExplorer:
If you don't see the left pane, clickExpand left pane to open the pane.
In theExplorer pane, expand the project, clickDatasets, andthen click the dataset that contains the table snapshot that you want to restore from.
ClickOverview> Tables, and then click the name of thetable snapshot.
In the table snapshot pane that appears, clickupdateRestore.
In theRestore snapshot pane that appears, enter theProject,Dataset, andTable information for the new table.
ClickSave.
SQL
Use theCREATE TABLE CLONE DDL statement:
In the Google Cloud console, go to theBigQuery page.
In the query editor, enter the following statement:
CREATETABLETABLE_PROJECT_ID.TABLE_DATASET_NAME.NEW_TABLE_NAMECLONESNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME;
Replace the following:
TABLE_PROJECT_ID: the project ID of the project in which to create the new table.TABLE_DATASET_NAME: the name of the dataset in which to create the new table.NEW_TABLE_NAME: the name of the new table.SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.SNAPSHOT_NAME: the name of the snapshot you are restoring from.
ClickRun.
For more information about how to run queries, seeRun an interactive query.
bq
Enter the following command in the Cloud Shell:
bqcp\--restore\--no_clobber\SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME\TABLE_PROJECT_ID:TABLE_DATASET_NAME.NEW_TABLE_NAME
Replace the following:
SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.SNAPSHOT_NAME: the name of the snapshot you are restoring from.TABLE_PROJECT_ID: the project ID of the project in which to create the new table.TABLE_DATASET_NAME: the name of the dataset in which to create the new table.NEW_TABLE_NAME: the name of the new table.
The--no_clobber flag instructs the command to fail if the destination tablealready exists.
API
Call thejobs.insertmethod with the following parameters:
| Parameter | Value |
|---|---|
projectId | The project ID of the project to bill for this operation. |
| Request body | {"configuration":{"copy":{"sourceTables":[{"projectId":"SNAPSHOT_PROJECT_ID","datasetId":"SNAPSHOT_DATASET_NAME","tableId":"SNAPSHOT_NAME"}],"destinationTable":{"projectId":"TABLE_PROJECT_ID","datasetId":"TABLE_DATASET_NAME","tableId":"NEW_TABLE_NAME"},"operationType":"RESTORE","writeDisposition":"WRITE_EMPTY"}}} |
Replace the following:
SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.SNAPSHOT_NAME: the name of the snapshot you are restoring from.TABLE_PROJECT_ID: the project ID of the project in which to create the new table.TABLE_DATASET_NAME: the name of the dataset in which to create the new table.NEW_TABLE_NAME: the name of the new table.
If an expiration is not specified, then the destination table expires after thedefault table expiration time for the dataset that contains the destinationtable.
Overwrite an existing table
You can overwrite an existing table with a table snapshot by using one of thefollowing options:
Console
In the Google Cloud console, go to theBigQuery page.
In the left pane, clickExplorer:
In theExplorer pane, expand the project, clickDatasets, andthen click the dataset that contains the table snapshot that you want to restore from.
ClickOverview> Tables, and then click the name of thetable snapshot.
In the table snapshot pane that appears, clickRestore.
In theRestore snapshot pane that appears, enter theProject,Dataset, andTable information for the existing table.
SelectOverwrite table if it exists.
ClickSave.
SQL
Use theCREATE TABLE CLONE DDL statement:
In the Google Cloud console, go to theBigQuery page.
In the query editor, enter the following statement:
CREATEORREPLACETABLETABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAMECLONESNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME;
Replace the following:
TABLE_PROJECT_ID: the project ID of the project in which to create the new table.TABLE_DATASET_NAME: the name of the dataset that contains the table you are overwriting.TABLE_NAME: the name of the table you are overwriting.SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.SNAPSHOT_NAME: the name of the snapshot you are restoring from.
ClickRun.
For more information about how to run queries, seeRun an interactive query.
bq
Enter the following command in the Cloud Shell:
bqcp\--restore\--force\SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME\TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME
Replace the following:
SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.SNAPSHOT_NAME: the name of the snapshot you are restoring from.TABLE_PROJECT_ID: the project ID of the project in which to create the new table.TABLE_DATASET_NAME: the name of the dataset that contains the table you are overwriting.TABLE_NAME: the name of the table you are overwriting.
API
Call thejobs.insertmethod with the following parameters:
| Parameter | Value |
|---|---|
projectId | The project ID of the project to bill for this operation. |
| Request body | {"configuration":{"copy":{"sourceTables":[{"projectId":"SNAPSHOT_PROJECT_ID","datasetId":"SNAPSHOT_DATASET_NAME","tableId":"SNAPSHOT_NAME"}],"destinationTable":{"projectId":"TABLE_PROJECT_ID","datasetId":"TABLE_DATASET_NAME","tableId":"TABLE_NAME"},"operationType":"RESTORE","writeDisposition":"WRITE_TRUNCATE"}}} |
Replace the following:
SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.SNAPSHOT_NAME: the name of the snapshot you are restoring from.TABLE_PROJECT_ID: the project ID of the project in which to create the new table.TABLE_DATASET_NAME: the name of the dataset that contains the table you are overwriting.TABLE_NAME: the name of the table you are overwriting.
If an expiration is not specified, then the destination table expires after thedefault table expiration time for the dataset that contains the destinationtable.
What's next
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-15 UTC.