Restoring table snapshots

This document describes how to restore a BigQuery table from a table snapshot by running a CREATE TABLE CLONE query, by using a bq copy --restore command, or by calling 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 restore a table snapshot, and the predefined IAM roles that grant those permissions.

Permissions

To restore a table snapshot, you need all of the following permissions:

Permission Resource
bigquery.tables.get The table snapshot that you want to restore.
bigquery.tables.getData The table snapshot that you want to restore.
bigquery.tables.restoreSnapshot The table snapshot that you want to restore.
bigquery.tables.create The dataset that contains the destination table.

Roles

The minimum predefined roles that you need to create a table snapshot are the following:

Role Resource
bigquery.dataEditor The table snapshot that you want to restore.
bigquery.dataEditor The dataset that contains the destination table.

Restore table snapshots

To restore a snapshot, specify the table snapshot that you want to restore from 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

For example, restore the table snapshot library_backup.books into the new table library.books_new as follows:

SQL

Run the following query:

CREATE TABLE library.books_new
  CLONE library_backup.books

bq

Enter the following command in the Cloud Shell:

Go to Cloud Shell

bq cp --restore --no_clobber library_backup.books library.books_new

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 myProject
Request body
{
  "configuration": {
    "copy": {
      "sourceTables": [
        {
          "projectId": "myProject",
          "datasetId": "library_backup",
          "tableId": "books"
        }
      ],
      "destinationTable": {
        "projectId": "myProject",
        "datasetId": "library",
        "tableId": "books_new"
      },
      "operationType": "RESTORE",
      "writeDisposition": "WRITE_EMPTY"
    }
  }
}

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; in this case, the library dataset.

Overwrite an existing table

Restore the table snapshot library_backup.books by overwriting the existing table library.books as follows:

SQL

Run the following query:

CREATE OR REPLACE TABLE
  library.books
  CLONE library_backup.books

bq

Enter the following command in the Cloud Shell:

Go to Cloud Shell

bq cp --restore --force library_backup.books library.books

API

Call the jobs.insert method with the following parameters:

Parameter Value
projectId myProject
Request body
{
  "configuration": {
    "copy": {
      "sourceTables": [
        {
          "projectId": "myProject",
          "datasetId": "library_backup",
          "tableId": "books"
        }
      ],
      "destinationTable": {
        "projectId": "myProject",
        "datasetId": "library",
        "tableId": "books"
      },
      "operationType": "RESTORE",
      "writeDisposition": "WRITE_TRUNCATE"
    }
  }
}

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; in this case, the library dataset.

Table security

To control access to tables in BigQuery, see Introduction to table access controls.

What's next