Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from http://cloud.google.com/sql/docs/sqlserver/backup-recovery/pitr below:

Use point-in-time recovery (PITR) | Cloud SQL for SQL Server

Stay organized with collections Save and categorize content based on your preferences.

This page describes how to use point-in-time recovery (PITR) to restore your primary Cloud SQL instance.

To learn more about PITR, see Point-in-time recovery (PITR).

If you create a Cloud SQL Enterprise Plus edition instance, then PITR is enabled by default, regardless of the method used for creation. If you want to disable the feature, then you must do so manually.

If you create a Cloud SQL Enterprise edition instance, then PITR is disabled by default, regardless of the method used for creation. In this case, if you want to enable the feature, then you must do so manually.

On May 31, 2024, we launched storing transaction logs for PITR in

. Since this launch, the following conditions apply:

• PITR-enabled Cloud SQL instances created before this date used to store PITR transaction logs on disk. PITR transaction logs for most of these instances have since been migrated to Cloud Storage. To verify the log location for a specific instance, see Check the storage location of transaction logs used for PITR.

  Note: If your Cloud SQL for SQL Server instance is on the old network architecture, the transaction logs for PITR might still remain on disk until the logs are migrated to the new network architecture.

• All Cloud SQL instances created with PITR enabled on or after this date store these logs in Cloud Storage.

Transaction logs update regularly and use storage space. Cloud SQL deletes transaction logs with their associated automatic backups automatically. This happens after the value set for thetransactionLogRetentionDays parameter is met. For more information about this parameter, see Logs and disk usage.

For instances that store transaction logs only on disk, you can configure Cloud SQL to store the logs on Cloud Storage by first deactivating and then re-enabling PITR. You can't move logs from Cloud Storage back to disk.

To ensure that logs for your instance are stored in Cloud Storage instead of on disk, complete the following actions:

• Check whether your instance stores logs on disk or Cloud Storage.
• Check the network architecture of the instance. If the instance is on the old network architecture, then upgrade it to the new network architecture.
• If the size of your logs on disk is causing performance issues for your instance, then deactivate PITR and re-enable it.

For instances having transaction logs stored in Cloud Storage, the logs are stored in the same region as the primary instance. This log storage (up to 35 days for Cloud SQL Enterprise Plus edition and seven days for Cloud SQL Enterprise edition, the maximum length for PITR) generates no additional cost per instance.

Cloud SQL generates transaction logs regularly and these logs use storage space. Cloud SQL deletes the transaction logs automatically with their associated automatic backups. This happens after the value that you set for the transactionLogRetentionDays parameter is met. This parameter specifies the number of days that Cloud SQL retains transaction logs. For Cloud SQL Enterprise Plus edition, you can set the number of days of retained transaction logs from 1 to 35, and for Cloud SQL Enterprise edition, you can set this value from 1 to 7.

If a value for this parameter isn't set, then the default transaction log retention period of 14 days is set for Cloud SQL Enterprise Plus edition instances and 7 days for Cloud SQL Enterprise edition instances. For more information on how to apply this setting, see Set transaction log retention.

To find out how much disk the transaction logs use, check the bytes_used_by_data_type metric for the instance. The value for the data type returns the size of the transaction logs on the disk. For instances that store transaction logs used for PITR on disk, Cloud SQL purges data from the disk daily to meet the transactionLogRetentionDays PITR setting. For more information, see Automated backup retention.

The following limitations are associated with your instance having PITR enabled and the size of your transaction logs on disk causing an issue for your instance:

• You can deactivate PITR and re-enable it to ensure that Cloud SQL stores logs in Cloud Storage in the same region as the instance. However, Cloud SQL deletes any existing logs so you can't perform a PITR operation earlier than the time that you re-enabled PITR.
• You can increase the instance storage size, but the transaction log size increase in disk usage might be temporary.
• To avoid unexpected storage issues, we recommend enabling automatic storage increases. This recommendation applies only if your instance has PITR enabled and your logs are stored on disk.
• If you want to delete logs and recover storage, then you can deactivate PITR without re-enabling it. However, decreasing the storage used doesn't shrink the size of the disk provisioned for the instance.

• Logs are purged once daily, not continuously. Setting the log retention to two days means that at least two days of logs, and at most three days of logs, are retained. We recommend setting the number of backups to one more than the days of log retention.

  For example, if you specify 7 for the value of the transactionLogRetentionDays parameter, then for the backupRetentionSettings parameter, set the number of retainedBackups to 8.

For more information about PITR, see Point-in-time recovery (PITR).

If you enable PITR on an instance, Cloud SQL automatically sets the recovery model of the existing and subsequent databases to the full recovery model.

For more information about SQL Server recovery models, see the Microsoft documentation.

When you create a new instance in the Google Cloud console, the

setting is automatically enabled.

The following procedure enables PITR on an existing primary instance.

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to enable PITR on and click Edit.
3. Under Customize your instance, expand the Data Protection section.
4. Select the Enable point-in-time recovery checkbox.
5. In the Days of logs field, enter the number of days to retain logs, from 1-35 for Cloud SQL Enterprise Plus edition, or 1-7 for Cloud SQL Enterprise edition.
6. Click Save.

1. Display the instance overview:

   gcloud sql instances describe INSTANCE_NAME

2. If you see enabled: false in the backupConfiguration section, enable scheduled backups:

   gcloud sql instances patch INSTANCE_NAME \
   --backup-start-time=HH:MM

   Specify the backup-start-time parameter using 24-hour time in UTC±00 time zone.

3. Enable PITR:

   gcloud sql instances patch INSTANCE_NAME \
   --enable-point-in-time-recovery

   If you're enabling PITR on a primary instance, you can also configure the number of days for which you want to retain transaction logs by adding the following parameter:

   --retained-transaction-log-days=RETAINED_TRANSACTION_LOG_DAYS

4. Confirm your change:

   gcloud sql instances describe INSTANCE_NAME

   In the backupConfiguration section, you see pointInTimeRecoveryEnabled: true if the change was successful.

To enable PITR, use a Terraform resource.

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

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.

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

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.

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

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,
      "pointInTimeRecoveryEnabled": true
    }
  }
}

To send your request, expand one of these options:

Save the request body in a file named request.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME"

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "OPERATION_ID",
  "targetId": "INSTANCE_NAME",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

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/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME

Request JSON body:

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

To send your request, expand one of these options:

Save the request body in a file named request.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME"

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "OPERATION_ID",
  "targetId": "INSTANCE_NAME",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

For an available instance, you can perform PITR to the latest time. If the instance is unavailable and the instance logs are stored in Cloud Storage, then you can retrieve the latest recovery time and perform the PITR to that time. In both cases, you can restore the instance to a different primary or secondary zone by providing values for the preferred zones.

Get the latest time to which you can recover a Cloud SQL instance that's not available.

Replace INSTANCE_NAME with the name of the instance that you're querying.

gcloud sql instances get-latest-recovery-time INSTANCE_NAME

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

• PROJECT_ID: the project ID
• INSTANCE_NAME: the name of the instance for which you're querying for the latest recovery time

HTTP method and URL:

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

To send your request, expand one of these options:

Execute the following command:

curl -X GET \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME/getLatestRecoveryTime"

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method GET `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME/getLatestRecoveryTime" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#getLatestRecoveryTime",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

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

• PROJECT_ID: the project ID
• INSTANCE_NAME: the name of the instance for which you're querying for the latest recovery time

HTTP method and URL:

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/getLatestRecoveryTime

To send your request, expand one of these options:

Execute the following command:

curl -X GET \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/getLatestRecoveryTime"

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method GET `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/getLatestRecoveryTime" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#getLatestRecoveryTime",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to recover and click Create clone.
3. Optionally, on the Create a clone page, update the ID of the new clone.
4. Select Clone from an earlier point in time.
5. Enter a PITR time.
6. Select All databases, or specify a database name.
   If you specify a database name, only one may be selected. By default, PITR is for all databases.
7. Click Create clone.

Create a clone using PITR.

Replace the following:

• SOURCE_INSTANCE_NAME - Name of the instance you're restoring from.
• NEW_INSTANCE_NAME - Name for the clone.
• TIMESTAMP - UTC timezone for the source instance in RFC 3339 format. For example, 2012-11-15T16:19:00.094Z.

gcloud sql instances clone SOURCE_INSTANCE_NAME \
NEW_INSTANCE_NAME \
--point-in-time 'TIMESTAMP'

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

• project-id: The project ID
• target-instance-id: The target instance ID
• source-instance-id: The source instance ID
• restore-timestamp The point-in-time to restore up to

In the JSON request, you optionally can specify up to one specific database name as the following: "databaseNames": "my-database"

HTTP method and URL:

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

Request JSON body:

{
  "cloneContext":
  {
    "kind": "sql#cloneContext",
    "destinationInstanceName": "target-instance-id",
    "pointInTime": "restore-timestamp"
  }
}

To send your request, expand one of these options:

Save the request body in a file named request.json, and execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/project-id/instances/source-instance-id/clone"

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/project-id/instances/source-instance-id/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/project-id/instances/target-instance-id",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE",
  "name": "operation-id",
  "targetId": "target-instance-id",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

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

• project-id: The project ID
• target-instance-id: The target instance ID
• source-instance-id: The source instance ID
• restore-timestamp The point-in-time to restore up to

In the JSON request, you optionally can specify up to one specific database name as the following: "databaseNames": "my-database"

HTTP method and URL:

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

Request JSON body:

{
  "cloneContext":
  {
    "kind": "sql#cloneContext",
    "destinationInstanceName": "target-instance-id",
    "pointInTime": "restore-timestamp"
  }
}

To send your request, expand one of these options:

Save the request body in a file named request.json, and execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/source-instance-id/clone"

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/source-instance-id/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/target-instance-id",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE",
  "name": "operation-id",
  "targetId": "target-instance-id",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Preview — Enhanced backups

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. You can process personal data for this feature as outlined in the Cloud Data Processing Addendum, subject to the obligations and restrictions described in the agreement under which you access Google Cloud. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

If your Cloud SQL instance is enabled to use enhanced backups, then you can perform point-in-time-recovery for your instance using the backup vault.

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to recover and click Create clone.

3. Select Clone from an earlier point in time.

4. Enter a PITR time.

5. Click Create clone.

To perform a PITR on an instance from the backup vault, you'll need to find the data-source for the backup that is nearest to the time you want to perform PITR. To find the backup, see List all the backups in the backup vault for an instance. Once you've identified the backup, run the following command to perform PITR:

gcloud sql instances point-in-time-restore DATA_SOURCE
PITR_TIMESTAMP
--project=TARGET_PROJECT

Replace the following:

• DATA_SOURCE: the path of the data-source for the backup that is closest to the PITR timestamp you want to recover to.
• PITR_TIMESTAMP: the UTC timestamp for the source instance PITR log you want to restore your instance to, in RFC 3339 format. For example, 2012-11-15T16:19:00.094Z.
• TARGET_PROJECT: the project ID of your Cloud SQL instance.

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to deactivate and select Edit.
3. Under Customize your instance, expand the Data Protection section.
4. Clear Enable point-in-time recovery.
5. Click Save.

1. Deactivate point-in-time recovery:

   gcloud sql instances patch INSTANCE_NAME \
   --no-enable-point-in-time-recovery

2. Confirm your change:

   gcloud sql instances describe INSTANCE_NAME

   In the backupConfiguration section, you see pointInTimeRecoveryEnabled: false if the change was successful.

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:

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

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "enabled": false,
      "pointInTimeRecoveryEnabled": false
    }
  }
}

To send your request, expand one of these options:

Save the request body in a file named request.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id"

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

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:

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

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "enabled": false,
      "pointInTimeRecoveryEnabled": false
    }
  }
}

To send your request, expand one of these options:

Save the request body in a file named request.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id"

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

You can check where your Cloud SQL instance is storing the transaction logs used for PITR.

To determine whether your instance stores logs for PITR on disk or Cloud Storage, use the following command:

   gcloud sql instances describe INSTANCE_NAME
   

Replace INSTANCE_NAME with the name of the instance.

For multiple instances in the same project, you can also check the storage location of the transaction logs. To determine the location for multiple instances, use the following command:

   gcloud sql instances list --show-transactional-log-storage-state
   

Example response:

NAME  DATABASE_VERSION         LOCATION       TRANSACTIONAL_LOG_STORAGE_STATE
my_01 SQLSERVER_2019_STANDARD  us-central-1   DISK
my_02 SQLSERVER_2019_STANDARD  us-central-1   CLOUD_STORAGE
...
   

In the output of the command, the transactionalLogStorageState field or the TRANSACTIONAL_LOG_STORAGE_STATE column provides information about where the transaction logs for PITR are stored for the instance. The possible transaction log storage states are the following:

• DISK: the instance stores the transaction logs used for PITR on disk.
• CLOUD_STORAGE: the instance stores the transaction logs used for PITR in Cloud Storage.

To set the number of days to retain transaction logs:

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to set the transaction log on and select Edit.
3. Under Customize your instance, expand the Data Protection section.
4. In the Enable point-in-time recovery section, expand Advanced options.
5. Enter the number of days to retain logs, from 1-35 for Cloud SQL Enterprise Plus edition or 1-7 for Cloud SQL Enterprise edition.
6. Click Save.

Edit the instance to set the number of days to retain transaction logs.

Replace the following:

• INSTANCE_NAME: The name of the instance you want to set the transaction log on.

• DAYS_TO_RETAIN: The number of days of transaction logs to keep. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days, with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is between 1 and 7 days, with a default of 7 days.

  If you don't specify a value, then Cloud SQL uses the default value. This is valid only when PITR is enabled. Keeping more days of transaction logs requires a bigger storage size.

  gcloud sql instances patch INSTANCE_NAME 

    --retained-transaction-log-days=DAYS_TO_RETAIN
  

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

• PROJECT_ID: the project ID.
• INSTANCE_ID: the instance ID.

• DAYS_TO_RETAIN: the number of days to retain transaction logs. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days, with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is between 1 and 7 days, with a default of 7 days.

  If no value is specified, then the default value is used. This is valid only when PITR is enabled. Keeping more days of transaction logs requires a bigger storage size.

HTTP method and URL:

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

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "transactionLogRetentionDays": "DAYS_TO_RETAIN"
    }
  }
}

To send your request, expand one of these options:

Save the request body in a file named request.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID"

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "OPERATION_ID",
  "targetId": "INSTANCE_ID",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

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

• PROJECT_ID: the project ID.
• INSTANCE_ID: the instance ID.

• DAYS_TO_RETAIN: the number of days to retain transaction logs. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days, with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is between 1 and 7 days, with a default of 7 days.

  If no value is specified, then the default value is used. This is valid only when PITR is enabled. Keeping more days of transaction logs requires a bigger storage size.

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "transactionLogRetentionDays": "DAYS_TO_RETAIN"
    }
  }
}

To send your request, expand one of these options:

Save the request body in a file named request.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID"

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }
Invoke-WebRequest `

    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "OPERATION_ID",
  "targetId": "INSTANCE_ID",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

argument --point-in-time: Failed to parse date/time:
Unknown string format: 2021-0928T30:54:03.094;
received: 2021-0928T30:54:03.094Z

OR

Invalid value at 'body.clone_context.point_in_time'
(type.googleapis.com/google.protobuf.Timestamp), Field 'pointInTime',
Invalid time format: Failed to parse input,

HTTP Error 400: Successful backup required for carrying out the operation was not found.

OR

Successful backup required for carrying out the operation was not found. or Time where no backups can be found.

• Configure flags on your clone

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-14 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-14 UTC."],[],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4