Restore a Cloud SQL instance from a backup vault

This document shows you how to restore a Cloud SQL instance from a backup vault using the Google Cloud console or the Google Cloud CLI. It describes how to restore to a new or existing instance, overwrite a source instance, or perform point-in-time recovery (PITR).

For more information about Backup and DR Service conceptual capabilities, see Backup and DR Service overview. If you want to backup your databases first, see Back up Cloud SQL instances to a backup vault.

Disaster recovery and resilience

Backup and DR Service provides disaster recovery options for your Cloud SQL instances. Backups stored in a backup vault are independent of the source instance's lifecycle and project.

  • Project deletion: you can restore backups even if the original workload project containing the source Cloud SQL instance has been deleted. This is because the necessary decryption permissions for CMEK-encrypted backups are granted to the Backup and DR service agent within the backup vault's project, not the workload project. This ensures that your backups remain accessible and restorable as long as the backup vault and the Cloud Key Management Service key are intact and the service agent has permissions.

  • Flexible restore targets: you can restore to a new instance in the same or a different project, providing flexibility in recovery scenarios.

Before you begin

Before you restore a Cloud SQL instance from a backup vault, ensure you have the required roles.

To get the permissions that you need to restore a Cloud SQL instance from a backup vault, ask your administrator to grant you the following IAM roles:

  • To create the restore target: Cloud SQL Admin (roles/cloudsql.admin) on the project where the target instance is located
  • To read the backup from the vault: Backup and DR Restore User (roles/backupdr.restoreUser) on the project where the source backups exist

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to restore a Cloud SQL instance from a backup vault. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to restore a Cloud SQL instance from a backup vault:

  • To create the restore target: cloudsql.instances.restore
  • To read the backup from the vault: backupdr.bvbackups.useReadOnlyForCloudSqlInstance

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

Restore a Cloud SQL instance from the most recent backup

Use the following instructions to restore a Cloud SQL instance:

Console

  1. In the Google Cloud console, go to the Vaulted backups page.

    Go to Vaulted backups

    The Vaulted backups page lists only the data resources that have backup plans applied and their backups stored in a backup vault within that project.

  2. Select the backup that you want to restore. Either from the backup details page for the backup, or from the menu, select Restore.

    The Restore page displays where you select the following restore options:

    • The Resource type is prefilled with Cloud SQL.

    • In Resource name, select the Cloud SQL instance to restore.

    • When you click Backup, a pane opens showing all available backups. Select the specific backup to restore.

    • For Choose the restore destination, select the project into which you want to restore the Cloud SQL instance.

  3. Click Continue.

  4. When you're prompted to Choose restore destination, make a selection from the following options:

    • Restore to a new instance: creates a new instance based on the configuration stored in your backup. You can customize the instance after the restore is complete. Provide a new Instance ID using lowercase letters, numbers, and hyphens. Start with a letter.

    • Overwrite an existing instance: select an existing instance to overwrite from the list of available instances.

    • Overwrite the source instance: overwrites your current instance. All the current data on the selected instance will be overwritten, including previous point-in-time recovery logs. The overwritten data cannot be recovered later. This is not recommended for production use.

gcloud

  1. Find the data source that contains the backup that you want to restore. You can find the data source from a Cloud SQL instance project or a backup vault project:

    • From Cloud SQL instance project: Use data-source-reference

      gcloud alpha backup-dr data-source-references fetch-for-resource-type sqladmin.googleapis.com/Instance \
--location=LOCATION \
--project=CSQL_INSTANCE_PROJECT_ID \
--filter='dataSourceGcpResourceInfo.cloudSqlInstanceProperties.name="projects/CSQL_INSTANCE_PROJECT_ID/instances/CSQL_INSTANCE_ID"' \
--format="table(RESOURCE_NAME,DATA_SOURCE)"

      Replace the following:

      • LOCATION: the location of the backup vault.

      • CSQL_INSTANCE_ID: the database name of the Cloud SQL instance.

      • CSQL_INSTANCE_PROJECT_ID: the project containing the Cloud SQL instance.

    • From a backup vault project: Use data-source

      gcloud backup-dr data-sources list \
--location=LOCATION \
--project=BACKUP_VAULT_PROJECT_ID \
--filter='dataSourceGcpResource.cloudSqlInstanceDatasourceProperties.name=projects/CSQL_INSTANCE_PROJECT_ID/instances/CSQL_INSTANCE_ID' \
--format="table(dataSourceGcpResource.cloudSqlInstanceDatasourceProperties.name:label=RESOURCE_NAME,name:label=DATA_SOURCE)"

      Replace the following:

      • LOCATION: the location of the backup vault.

      • CSQL_INSTANCE_ID: the name of the Cloud SQL instance.

      • CSQL_INSTANCE_PROJECT_ID: the project containing the Cloud SQL instance.

      • BACKUP_VAULT_PROJECT_ID: the project containing the backup vault.

  2. List and select the backup to restore by listing all the backups in the data source using the gcloud backup-dr backups list command.

    gcloud backup-dr backups list --data-source=DATA_SOURCE \
--format="table(consistencyTime,backupType,name:label=BACKUP_NAME)"

  3. Restore a Cloud SQL instance using the gcloud sql backups restore command:

    gcloud sql backups restore BACKUP_NAME \
--restore-instance=RESTORE_INSTANCE \
--project=RESTORE_PROJECT
--region=RESTORE_REGION

    Replace the following:

    • BACKUP_NAME: the backup vault name you want to restore data from.

    • RESTORE_INSTANCE: the target Cloud SQL instance name.

    • RESTORE_PROJECT: the project where you want to restore the Cloud SQL instance.

    • RESTORE_REGION: the region where you want to restore to a new Cloud SQL instance. This is optional for regional backups. Don't use when restoring to an existing instance.

  4. Optional: to override other properties, see Overview of Backup and DR Service Google Cloud CLI commands.

Restore a Cloud SQL instance to a different point-in-time

Use the following instructions to restore a Cloud SQL instance to a point in time. When you restore to a point in time, you create a Cloud SQL clone.

  1. In the Google Cloud console, go to the Vaulted backups page.

    Go to Vaulted backups

  2. At the top of the table, select Filter table and then click Resource type. Select Cloud SQL. This limits the display to only the Cloud SQL instances that have backup plans applied and their backups stored in a backup vault within a project.

  3. Click Restore from a point in time. The Cloud SQL Create a clone page opens.

  4. Follow the procedures in Perform PITR by using the backup vault.

What's next

For more information about granting roles, see Manage access to projects, folders, and organizations.