Create and manage on-demand and automatic backups

This page describes how to schedule and turn off automatic backups, create and manage on-demand backups, and view backups for your Cloud SQL instance.

For more information about how backups work, see Overview of backups.

Create an on-demand backup

To create an on-demand backup:

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Backups from the SQL navigation menu.
  4. Click Create backup.
  5. On the Create backup page, add a description, if needed, and click Create.

gcloud

Create the backup:

gcloud sql backups create \
--async \
--instance=INSTANCE_NAME

You can provide a description of the backup using the --description parameter.

To create a backup in a custom location:

gcloud sql backups create \
--async \
--instance=INSTANCE_NAME \
--location=BACKUP_LOCATION

REST v1

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

POST https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/backupRuns

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/backupRuns

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Schedule automated backups

To schedule automated backups for an instance:

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Backups from the SQL navigation menu.
  4. Click Edit, next to Settings.
  5. Choose a window of time for your data to be automatically backed up.
  6. Click Save.

gcloud

  1. Edit the instance to specify a backup start time: 
    gcloud sql instances patch INSTANCE_NAME --backup-start-time=HH:MM
     The backup-start-time parameter is specified in a 24-hour time format, in the UTC±00 time zone, and specifies the start of a 4-hour backup window. Backups can start any time during the backup window.
  2. Confirm your changes: 
    gcloud sql instances describe INSTANCE_NAME
     In the backupConfiguration section, confirm that you see enabled: true and the time that you specified.

Terraform

To create a backup for a database, use a Terraform resource. 

resource "google_sql_database_instance" "default" {
  name             = "sqlserver-instance-backup"
  region           = "us-central1"
  database_version = "SQLSERVER_2019_STANDARD"
  root_password    = "INSERT-PASSWORD-HERE"
  settings {
    tier = "db-custom-2-7680"
    backup_configuration {
      enabled    = true
      start_time = "20:55"
    }
  }
  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by
  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.
  deletion_protection = false
}

Apply the changes

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

  1. Launch Cloud Shell.

  2. Set the default Google Cloud project where you want to apply your Terraform configurations.

    You only need to run this command once per project, and you can run it in any directory.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

  1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. If you are following a tutorial, you can copy the sample code in each section or step.

    Copy the sample code into the newly created main.tf.

    Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. Initialize Terraform. You only need to do this once per directory. 
    terraform init

    Optionally, to use the latest Google provider version, include the -upgrade option: 

    terraform init -upgrade

Apply the changes

  1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations: 
    terraform plan

    Make corrections to the configuration as necessary.

  2. Apply the Terraform configuration by running the following command and entering yes at the prompt: 
    terraform apply

    Wait until Terraform displays the "Apply complete!" message.

  3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Delete the changes

To delete your changes, do the following:

  1. To disable deletion protection, in your Terraform configuration file set the deletion_protection argument to false. 
    deletion_protection =  "false"
  2. Apply the updated Terraform configuration by running the following command and entering yes at the prompt: 
    terraform apply

  1. Remove resources previously applied with your Terraform configuration by running the following command and entering yes at the prompt:

    terraform destroy

REST v1

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
  • INSTANCE_NAME: the name of the primary or read replica instance that you're configuring for high availability
  • START_TIME: the time (in hours and minutes)

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME

Request JSON body: 

{
  "settings":
  {
    "backupConfiguration":
    {
      "startTime": "START_TIME",
      "enabled": true,
      "binaryLogEnabled": true
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Set a custom location for backups

Only use a custom backup location if required by regulation. If not required, use the default multi-region backup location.

You can use a custom location for on-demand and automatic backups. For a complete list of valid location values, see the Instance locations.

To update an instance with a custom location:

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. From the SQL navigation menu, select Backups.
  4. Next to Settings, click Edit.
  5. In the Automated Backups section, expand Advanced Options.
  6. Click Multi-region (default) or Region.
  7. Select the location from the Location drop-down menu.
  8. Click Save.

gcloud

For reference information, see gcloud sql instances patch. 

gcloud sql instances patch INSTANCE_NAME \
--backup-location=BACKUP_LOCATION

Terraform

To specify a custom location for a database instance backup, use a Terraform resource. 

resource "google_sql_database_instance" "default" {
  name             = "sqlserver-instance-with-backup-location"
  region           = "us-central1"
  database_version = "SQLSERVER_2019_STANDARD"
  root_password    = "INSERT-PASSWORD-HERE"
  settings {
    tier = "db-custom-2-7680"
    backup_configuration {
      enabled  = true
      location = "us-central1"
    }
  }
  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by
  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.
  deletion_protection = false
}

REST v1

Use the instance's settings.backupConfiguration.location parameter for the backup location.

Before using any of the request data, make the following replacements:

  • region: The project region
  • backup-region: The backup region
  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id

Request JSON body: 

{
  "region": "region"
  "settings":
  {
    "backupConfiguration":
    {
      "location": "backup-region",
      "enabled": true,
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

Use the instance's settings.backupConfiguration.location parameter for the backup location.

Before using any of the request data, make the following replacements:

  • region: The project region
  • backup-region: The backup region
  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body: 

{
  "region": "region"
  "settings":
  {
    "backupConfiguration":
    {
      "location": "backup-region",
      "enabled": true,
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

View a list of backups

To view a list of backups and details about a backup, use the following options.

The Google Cloud console does not show skipped or failed backups in the backup history. To see skipped backups, use gcloud or the API.

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Backups from the SQL navigation menu to view recent backups, along with creation time and type of backup.

gcloud

  • List the backups of the instance: 
    gcloud sql backups list \
--instance INSTANCE_NAME

    You can filter and control the results using the standard list parameters. For a complete list, see the gcloud sql backups list command reference page.

  • View the details of a backup by specifying the backup ID from the output of the backups list command. 
    gcloud sql backups describe BACKUP_ID \
--instance INSTANCE_NAME

REST v1

You can also use the APIs Explorer on the BackupRuns:list page to send the REST API request.

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

GET https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/backupRuns

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

REST v1beta4

You can also use the APIs Explorer on the BackupRuns:list page to send the REST API request.

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/backupRuns

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

View a list of backups during an outage

When an instance is in a region that is having an outage, you can only view backups for that instance using a wildcard (-) with the backupRuns.list API. The wildcard returns all backups in the project. See Restoring to a different instance.

When there's an outage in a region where an instance has customer-managed encryption keys (CMEK) enabled, you can't restore the backups for that instance to a different region. The reason is because Cloud SQL uses regional Cloud KMS keys for encryption when CMEK is enabled on the instance. To restore backups for a CMEK instance, Cloud SQL must have access to the CMEK key. In a regional outage, there's no access to the Cloud KMS keys in that region.

gcloud

  1. List the backups for every instance in a project: 
    gcloud sql backups list --instance -

    The command returns information similar to the following:

    {
      "kind": "sql#backupRun",
      "status": "SUCCESSFUL",
      "enqueuedTime": "2020-01-21T11:25:33.818Z",
      "id": "backup-id",
      "startTime": "2020-01-21T11:25:33.858Z",
      "endTime": "2020-01-21T11:26:18.663Z",
      "type": "AUTOMATED",
      "windowStartTime": "2020-01-21T10:00:00.479Z",
      "instance": "instance-id",
      "selfLink": "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/backupRuns/backup-id",
      "location": "us"
      "backupKind": "SNAPSHOT"
    }
  2. Find the backup for the instance you need. To restore this backup, see Restoring to a different instance.

REST v1

  1. List all the backups in a project:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID, or - for a list of all backups in the project

    HTTP method and URL: 

    GET https://sqladmin.googleapis.com/v1/projects/project-id/instances/-/backupRuns

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  2. Find the backup for the instance you need. To restore this backup, see Restoring to a different instance.

REST v1beta4

  1. List all the backups in a project:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID, or - for a list of all backups in the project

    HTTP method and URL: 

    GET https://sqladmin.googleapis.com/v1beta4/projects/project-id/instances/-/backupRuns

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  2. Find the backup for the instance you need. To restore this backup, see Restoring to a different instance.

View backup locations

To see the locations of the backups taken for an instance, use backupRuns:

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Backups from the SQL navigation menu.
  4. In the backups list, the Location column lists the type of location (multi-region or region) and the specific multi-region or region for each backup.

gcloud

For reference information, see gcloud sql instances describe.

  1. List the backups of the instance: 
    gcloud sql backups list \
--instance INSTANCE_NAME

    You can filter and control the results using the standard list parameters. For a complete list, see the gcloud sql backups list command reference page.

  2. To list the details of one backup, use the ID from the output of the backups list command. 
    gcloud sql backups describe BACKUP_ID \
--instance INSTANCE_NAME

REST v1

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

GET https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/backupRuns

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

In the previous examples:
  • REGION is the region for the original instance.
  • BACKUP_LOCATION is the location where you want Cloud SQL to store the backup.

REST v1beta4

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/backupRuns

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

In the previous examples:
  • REGION is the region for the original instance.
  • BACKUP_LOCATION is the location where you want Cloud SQL to store the backup.

Set automated backup retention

To set the number of automated backups to retain:

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Backups from the SQL navigation menu.
  4. Click Edit.
  5. In the Automate backups section, expand Advanced options.
  6. Enter the Number of backups to store at a time.

    The number can't be less than the default (seven).

  7. Click Save.

gcloud

Edit the instance to set the number of automated backups to retain. The number can't be less than the default (seven). 

gcloud sql instances patch INSTANCE_NAME \
--retained-backups-count=NUM_TO_RETAIN

Terraform

To specify the number of automated backups to retain for a database instance, use a Terraform resource. 

resource "google_sql_database_instance" "default" {
  name             = "sqlserver-instance-backup-retention"
  region           = "us-central1"
  database_version = "SQLSERVER_2019_STANDARD"
  root_password    = "INSERT-PASSWORD-HERE"
  settings {
    tier = "db-custom-2-7680"
    backup_configuration {
      enabled = true
      backup_retention_settings {
        retained_backups = 365
        retention_unit   = "COUNT"
      }
    }
  }
  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by
  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.
  deletion_protection = false
}

REST v1beta4

Before using any of the request data, make the following replacements:

  • unit: Optional: The retention unit can either be an integer or a string. The currently supported (and default) values are 1 or "COUNT".
  • num-to-retain: The number of automated backups to retain. The number can't be less than the default (7).
  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body: 

{
  "settings":
  {
    "backupConfiguration":
    {
      "backupRetentionSettings":
      {
        "retentionUnit": unit,
        "retainedBackups": "num-to-retain"
      }
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Delete a backup

You can delete automated and on-demand backups.

Deleting a backup might not free up as much space as the size of the backup. This is because backups are incremental, so deleting an older backup might transfer some of its contents to a more recent backup to preserve the integrity of the newer backup.

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Backups from the SQL navigation menu.
  4. Click more actions More actions icon. for the backup you want to delete.
  5. Select Delete.
  6. In the Delete backup window, type Delete into the field and click Delete.

gcloud

Delete a backup of a Cloud SQL instance:

gcloud beta sql backups delete BACKUP_ID \
--instance INSTANCE_NAME

For a complete list of parameters, see the gcloud beta sql backups delete command reference page.

REST v1

  1. List the backups to obtain the ID for the backup you want to delete:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID

    HTTP method and URL: 

    GET https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/backupRuns

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  2. Delete the backup:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • backup-id: The backup ID

    HTTP method and URL: 

    DELETE https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/backupRuns/backup-id

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

REST v1beta4

  1. List the backups to obtain the ID for the backup you want to delete:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID

    HTTP method and URL: 

    GET https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/backupRuns

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  2. Delete the backup:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • backup-id: The backup ID

    HTTP method and URL: 

    DELETE https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/backupRuns/backup-id

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

Disable automated backups

To disable automated backups for an instance:

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Backups from the SQL navigation menu.
  4. Click Manage automated backups
  5. Clear Automate backups.
  6. Click Save.

gcloud

Edit the instance to disable backups:

gcloud sql instances patch INSTANCE_NAME \
--no-backup

REST v1

REST v1beta4

What's next