Restore deleted datasets

This document describes how to restore (or undelete) a deleted dataset in BigQuery.

You can restore a dataset to recover it to the state that it was in when it was deleted. You can only restore datasets that are within your time travel window. This recovery includes all of the objects that were contained in the dataset, the dataset properties, and the security settings. For resources that are not recovered, see Limitations.

For information about restoring a deleted table or snapshot, see the following resources:

Limitations

The following is a list of limitations related to restoring a dataset:

  • Restored datasets might reference security principals that no longer exist.
  • References to a deleted dataset in linked datasets aren't restored when you perform this action. Subscribers must subscribe again to manually restore the links.
  • Business tags aren't restored when you perform this action.
  • You must manually refresh materialized views and reauthorize authorized views, authorized datasets, and authorized routines.

  • A BigQuery CDC-enabled table doesn't resume background apply jobs when restored as part of an undeleted dataset.

    When authorized resources (views, datasets, and routines) are deleted, it takes up to 24 hours for the authorization to delete. So, if you restore a dataset with an authorized resource less than 24 hours after deletion, it's possible that reauthorization isn't necessary. As a best practice, always verify authorization after restoring resources.

Before you begin

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

Required roles

To get the permissions that you need to restore a deleted dataset, 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.

This predefined role contains the permissions required to restore a deleted dataset. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to restore a deleted dataset:

  • bigquery.datasets.create on the project
  • bigquery.datasets.get on the dataset

You might also be able to get these permissions with custom roles or other predefined roles.

Restore a dataset

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

SQL

Use the UNDROP SCHEMA data definition language (DDL) statement:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    UNDROP SCHEMA DATASET_ID;

    Replace DATASET_ID with the dataset that you want to undelete.

  3. Specify the location of the dataset that you want to undelete.

  4. Click Run.

For more information about how to run queries, see Run an interactive query.

API

Call the datasets.undelete method.

When you restore a dataset, the following errors might occur:

  • ALREADY_EXISTS: a dataset with the same name already exists in the region in which you tried to restore. You can't use undelete to overwrite or merge datasets.
  • NOT_FOUND: the dataset you're trying to recover is past its time travel window, it never existed, or you didn't specify the correct location of the dataset.

  • ACCESS_DENIED: you don't have the required permissions to undelete this dataset.

    What's next

  • For information about querying data at a point in time, see Access historical data.

  • For information about data retention, see Data retention with time travel and fail-safe.

  • For information about how to delete a dataset, see Manage datasets.