Restore table snapshots
This document describes how to create a writeable table from a
table snapshot by using the Trusted Cloud console, a CREATE TABLE CLONE query, a
bq cp command, or the jobs.insert API.
It is intended for users who are familiar with
table snapshots.
Permissions and roles
This section describes the
Identity and Access Management (IAM) permissions
that you need to create a writeable table from a table snapshot, and the
predefined IAM roles
that grant those permissions.
Permissions
To create a writeable table from a table snapshot, you need the following
permissions:
| Permission |
Resource |
All of the following:
bigquery.tables.get
bigquery.tables.getData
bigquery.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 required
permissions are as follows:
| Role |
Resource |
Any of the following:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
|
The table snapshot that you want to copy into a writeable table. |
Any of the following:
bigquery.dataEditor
bigquery.dataOwner
bigquery.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 you
want to copy and the destination table. The destination table can be a new
table, 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 Trusted Cloud console, go to the BigQuery page.
Go to BigQuery
In the Explorer pane, expand the project and dataset nodes of the
table snapshot you want to restore from.
Click the name of the table snapshot.
In the table snapshot pane that appears, click Restore.
In the Restore snapshot pane that appears, enter the Project,
Dataset, and Table information for the new table.
Click Save.
SQL
Use the
CREATE TABLE CLONE DDL statement:
In the Trusted Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:
CREATE TABLE TABLE_PROJECT_ID.TABLE_DATASET_NAME.NEW_TABLE_NAME
CLONE SNAPSHOT_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.
Click play_circle Run.
For more information about how to run queries, see Run an interactive query.
bq
Enter the following command in the Cloud Shell:
Go to Cloud Shell
bq cp \
--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 table
already exists.
API
Call the
jobs.insert
method 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 the
default table expiration time for the dataset that contains the destination
table.
Overwrite an existing table
You can overwrite an existing table with a table snapshot by using one of the
following options:
Console
In the Trusted Cloud console, go to the BigQuery page.
Go to BigQuery
In the Explorer pane, expand the project and dataset nodes of the
table snapshot you want to restore from.
Click the name of the table snapshot.
In the table snapshot pane that appears, click Restore.
In the Restore snapshot pane that appears, enter the Project,
Dataset, and Table information for the existing table.
Select Overwrite table if it exists.
Click Save.
SQL
Use the
CREATE TABLE CLONE DDL statement:
In the Trusted Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:
CREATE OR REPLACE TABLE TABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAME
CLONE SNAPSHOT_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.
Click play_circle Run.
For more information about how to run queries, see Run an interactive query.
bq
Enter the following command in the Cloud Shell:
Go to Cloud Shell
bq cp \
--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 the
jobs.insert
method 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 the
default table expiration time for the dataset that contains the destination
table.
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-08-25 UTC.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-25 UTC."],[[["\u003cp\u003eThis document details the process of creating a writeable table from a table snapshot in Google Cloud, providing guidance for users familiar with table snapshots.\u003c/p\u003e\n"],["\u003cp\u003eCreating a writeable table from a snapshot requires specific IAM permissions such as \u003ccode\u003ebigquery.tables.get\u003c/code\u003e, \u003ccode\u003ebigquery.tables.getData\u003c/code\u003e, \u003ccode\u003ebigquery.tables.restoreSnapshot\u003c/code\u003e, and \u003ccode\u003ebigquery.tables.create\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eYou can restore a table snapshot to either a new table or overwrite an existing one using the Google Cloud console, SQL's \u003ccode\u003eCREATE TABLE CLONE\u003c/code\u003e statement, \u003ccode\u003ebq cp\u003c/code\u003e command, or the \u003ccode\u003ejobs.insert\u003c/code\u003e API.\u003c/p\u003e\n"],["\u003cp\u003eTo overwrite an existing table, ensure you use \u003ccode\u003eCREATE OR REPLACE TABLE\u003c/code\u003e in SQL or include the \u003ccode\u003e--force\u003c/code\u003e flag in the \u003ccode\u003ebq cp\u003c/code\u003e command, or use \u003ccode\u003e"writeDisposition": "WRITE_TRUNCATE"\u003c/code\u003e in the API call.\u003c/p\u003e\n"],["\u003cp\u003eThe user needs the appropriate predefined roles in order to copy the table snapshot, such as \u003ccode\u003ebigquery.dataEditor\u003c/code\u003e, \u003ccode\u003ebigquery.dataOwner\u003c/code\u003e, or \u003ccode\u003ebigquery.admin\u003c/code\u003e.\u003c/p\u003e\n"]]],[],null,["# Restore table snapshots\n=======================\n\nThis document describes how to create a writeable table from a\ntable snapshot by using the Google Cloud console, a `CREATE TABLE CLONE` query, a\n`bq cp` command, or the `jobs.insert` API.\nIt is intended for users who are familiar with\n[table snapshots](/bigquery/docs/table-snapshots-intro).\n\nPermissions and roles\n---------------------\n\nThis section describes the\n[Identity and Access Management (IAM) permissions](/bigquery/docs/access-control#bq-permissions)\nthat you need to create a writeable table from a table snapshot, and the\n[predefined IAM roles](/bigquery/docs/access-control#bigquery)\nthat grant those permissions.\n\n### Permissions\n\nTo create a writeable table from a table snapshot, you need the following\npermissions:\n\n### Roles\n\nThe predefined BigQuery roles that provide the required\npermissions are as follows:\n\nRestore a table snapshot\n------------------------\n\nTo create a writeable table from a snapshot, specify the table snapshot that you\nwant to copy and the destination table. The destination table can be a new\ntable, or you can overwrite an existing table with the table snapshot.\n\n### Restore to a new table\n\nYou can restore a table snapshot into a new table by using one of the following options: \n\n### Console\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** pane, expand the project and dataset nodes of the\n table snapshot you want to restore from.\n\n3. Click the name of the table snapshot.\n\n4. In the table snapshot pane that appears, click **Restore**.\n\n5. In the **Restore snapshot** pane that appears, enter the **Project** ,\n **Dataset** , and **Table** information for the new table.\n\n6. Click **Save**.\n\n### SQL\n\nUse the\n[`CREATE TABLE CLONE` DDL statement](/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement):\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the query editor, enter the following statement:\n\n ```googlesql\n CREATE TABLE TABLE_PROJECT_ID.TABLE_DATASET_NAME.NEW_TABLE_NAME\n CLONE SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME;\n ```\n\n\n Replace the following:\n - \u003cvar translate=\"no\"\u003eTABLE_PROJECT_ID\u003c/var\u003e: the project ID of the project in which to create the new table.\n - \u003cvar translate=\"no\"\u003eTABLE_DATASET_NAME\u003c/var\u003e: the name of the dataset in which to create the new table.\n - \u003cvar translate=\"no\"\u003eNEW_TABLE_NAME\u003c/var\u003e: the name of the new table.\n - \u003cvar translate=\"no\"\u003eSNAPSHOT_PROJECT_ID\u003c/var\u003e: the project ID of the project that contains the snapshot you are restoring from.\n - \u003cvar translate=\"no\"\u003eSNAPSHOT_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the snapshot you are restoring from.\n - \u003cvar translate=\"no\"\u003eSNAPSHOT_NAME\u003c/var\u003e: the name of the snapshot you are restoring from.\n\n \u003cbr /\u003e\n\n3. Click play_circle **Run**.\n\n \u003cbr /\u003e\n\nFor more information about how to run queries, see [Run an interactive query](/bigquery/docs/running-queries#queries).\n\n### bq\n\nEnter the following command in the Cloud Shell:\n\n[Go to Cloud Shell](https://console.cloud.google.com/bigquery?cloudshell=true) \n\n```bash\nbq cp \\\n--restore \\\n--no_clobber \\\nSNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME \\\nTABLE_PROJECT_ID:TABLE_DATASET_NAME.NEW_TABLE_NAME\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_PROJECT_ID\u003c/var\u003e: the project ID of the project that contains the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_NAME\u003c/var\u003e: the name of the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eTABLE_PROJECT_ID\u003c/var\u003e: the project ID of the project in which to create the new table.\n- \u003cvar translate=\"no\"\u003eTABLE_DATASET_NAME\u003c/var\u003e: the name of the dataset in which to create the new table.\n- \u003cvar translate=\"no\"\u003eNEW_TABLE_NAME\u003c/var\u003e: the name of the new table.\n\n\u003cbr /\u003e\n\nThe `--no_clobber` flag instructs the command to fail if the destination table\nalready exists.\n\n### API\n\nCall the\n[`jobs.insert`](/bigquery/docs/reference/rest/v2/jobs/insert)\nmethod with the following parameters:\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_PROJECT_ID\u003c/var\u003e: the project ID of the project that contains the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_NAME\u003c/var\u003e: the name of the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eTABLE_PROJECT_ID\u003c/var\u003e: the project ID of the project in which to create the new table.\n- \u003cvar translate=\"no\"\u003eTABLE_DATASET_NAME\u003c/var\u003e: the name of the dataset in which to create the new table.\n- \u003cvar translate=\"no\"\u003eNEW_TABLE_NAME\u003c/var\u003e: the name of the new table.\n\n\u003cbr /\u003e\n\nIf an expiration is not specified, then the destination table expires after the\ndefault table expiration time for the dataset that contains the destination\ntable.\n\n### Overwrite an existing table\n\nYou can overwrite an existing table with a table snapshot by using one of the\nfollowing options: \n\n### Console\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** pane, expand the project and dataset nodes of the\n table snapshot you want to restore from.\n\n3. Click the name of the table snapshot.\n\n4. In the table snapshot pane that appears, click **Restore**.\n\n5. In the **Restore snapshot** pane that appears, enter the **Project** ,\n **Dataset** , and **Table** information for the existing table.\n\n6. Select **Overwrite table if it exists**.\n\n7. Click **Save**.\n\n### SQL\n\nUse the\n[`CREATE TABLE CLONE` DDL statement](/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement):\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the query editor, enter the following statement:\n\n ```googlesql\n CREATE OR REPLACE TABLE TABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAME\n CLONE SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME;\n ```\n\n\n Replace the following:\n - \u003cvar translate=\"no\"\u003eTABLE_PROJECT_ID\u003c/var\u003e: the project ID of the project in which to create the new table.\n - \u003cvar translate=\"no\"\u003eTABLE_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the table you are overwriting.\n - \u003cvar translate=\"no\"\u003eTABLE_NAME\u003c/var\u003e: the name of the table you are overwriting.\n - \u003cvar translate=\"no\"\u003eSNAPSHOT_PROJECT_ID\u003c/var\u003e: the project ID of the project that contains the snapshot you are restoring from.\n - \u003cvar translate=\"no\"\u003eSNAPSHOT_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the snapshot you are restoring from.\n - \u003cvar translate=\"no\"\u003eSNAPSHOT_NAME\u003c/var\u003e: the name of the snapshot you are restoring from.\n\n \u003cbr /\u003e\n\n3. Click play_circle **Run**.\n\n \u003cbr /\u003e\n\nFor more information about how to run queries, see [Run an interactive query](/bigquery/docs/running-queries#queries).\n\n### bq\n\nEnter the following command in the Cloud Shell:\n\n[Go to Cloud Shell](https://console.cloud.google.com/bigquery?cloudshell=true) \n\n```bash\nbq cp \\\n--restore \\\n--force \\\nSNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME \\\nTABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_PROJECT_ID\u003c/var\u003e: the project ID of the project that contains the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_NAME\u003c/var\u003e: the name of the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eTABLE_PROJECT_ID\u003c/var\u003e: the project ID of the project in which to create the new table.\n- \u003cvar translate=\"no\"\u003eTABLE_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the table you are overwriting.\n- \u003cvar translate=\"no\"\u003eTABLE_NAME\u003c/var\u003e: the name of the table you are overwriting.\n\n\u003cbr /\u003e\n\n### API\n\nCall the\n[`jobs.insert`](/bigquery/docs/reference/rest/v2/jobs/insert)\nmethod with the following parameters:\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_PROJECT_ID\u003c/var\u003e: the project ID of the project that contains the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eSNAPSHOT_NAME\u003c/var\u003e: the name of the snapshot you are restoring from.\n- \u003cvar translate=\"no\"\u003eTABLE_PROJECT_ID\u003c/var\u003e: the project ID of the project in which to create the new table.\n- \u003cvar translate=\"no\"\u003eTABLE_DATASET_NAME\u003c/var\u003e: the name of the dataset that contains the table you are overwriting.\n- \u003cvar translate=\"no\"\u003eTABLE_NAME\u003c/var\u003e: the name of the table you are overwriting.\n\n\u003cbr /\u003e\n\nIf an expiration is not specified, then the destination table expires after the\ndefault table expiration time for the dataset that contains the destination\ntable.\n\nWhat's next\n-----------\n\n- [List the table snapshots of a specified base table](/bigquery/docs/table-snapshots-list#list_the_table_snapshots_of_a_specified_base_table)."]]
