This page describes how to use point-in-time recovery (PITR) to restore your
primary Cloud SQL instance.
To learn more about PITR, see
PITR .
Log storage for PITR
PITR uses transaction logs. These logs update
regularly and use storage space. The transaction logs are automatically
deleted with their associated automatic backup, which generally happens
after about 7 days.
If the size of your transaction logs are causing an issue for
your instance:
You can increase the instance storage size, but the transaction
log size increase in disk usage might be temporary.
We recommend enabling
automatic storage increases to avoid unexpected storage issues.
You can disable PITR if you want to delete logs and recover
storage. Decreasing the storage used doesn't shrink the size of the storage
provisioned for the instance.
Logs are purged once daily, not continuously. Setting 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 to guarantee a minimum of specified days of log retention.
Database recovery model for PITR
When you enable
PITR on an instance, Cloud SQL automatically sets the recovery model of
the existing and subsequent databases to the full recovery model.
Caution: After enabling PITR on your instance, don't change the
database recovery model to simple. This can break the
transaction log backup chain, and you might not be able to recover the instance data to the
point in time when the recovery model was set to simple.
For more information about SQL Server recovery models, see the
Microsoft documentation .
Enable PITR
When you create a new instance in the Google Cloud console, the
Automated
backups setting is automatically enabled.
The following procedure enables PITR on an
existing instance.
Console
In the Google Cloud console, go to the Cloud SQL Instances page.
Go to Cloud SQL Instances
Open the more actions menu for the instance you
want to enable PITR on and click Edit .
Under Customize your instance , expand the
Data Protection section.
Select the Enable point-in-time recovery checkbox.
Expand Advanced options .
Enter the number of days to retain logs, from 1-7.
Click Save .
gcloud
Display the instance overview:
gcloud sql instances describe INSTANCE_NAME
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.
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
Confirm your change:
gcloud sql instances describe INSTANCE_NAME
In the backupConfiguration
section, you see
pointInTimeRecoveryEnabled: true
if the change was successful.
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,
"pointInTimeRecoveryEnabled": true
}
}
}
To send your request, expand one of these options:
curl (Linux, macOS, or Cloud Shell)
Note:
The following command assumes that you have logged in to
the gcloud
CLI with your user account by running
gcloud init
or
gcloud auth login
, or by using Cloud Shell ,
which automatically logs you into the gcloud
CLI
.
You can check the currently active account by running
gcloud auth list
.
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 "
PowerShell (Windows)
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:
Response
{
"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 "
}
REST v1beta4
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:
curl (Linux, macOS, or Cloud Shell)
Note:
The following command assumes that you have logged in to
the gcloud
CLI with your user account by running
gcloud init
or
gcloud auth login
, or by using Cloud Shell ,
which automatically logs you into the gcloud
CLI
.
You can check the currently active account by running
gcloud auth list
.
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 "
PowerShell (Windows)
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:
Response
{
"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 "
}
Console
In the Google Cloud console, go to the Cloud SQL Instances page.
Go to Cloud SQL Instances
Open the more actions menu for the instance you
want to recover and click Create clone .
Optionally, on the Create a clone page, update the ID of the
new clone.
Select Clone from an earlier point in time .
Enter a PITR time.
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.
Click Create clone .
gcloud
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 '
Note: By default, point-in-time recovery is for all
databases. Optionally, you can specify up to one specific
database name as follows: --database_names 'DATABASE'
REST v1
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:
curl (Linux, macOS, or Cloud Shell)
Note:
The following command assumes that you have logged in to
the gcloud
CLI with your user account by running
gcloud init
or
gcloud auth login
, or by using Cloud Shell ,
which automatically logs you into the gcloud
CLI
.
You can check the currently active account by running
gcloud auth list
.
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"
PowerShell (Windows)
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:
Response
{
"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 "
}
REST v1beta4
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:
curl (Linux, macOS, or Cloud Shell)
Note:
The following command assumes that you have logged in to
the gcloud
CLI with your user account by running
gcloud init
or
gcloud auth login
, or by using Cloud Shell ,
which automatically logs you into the gcloud
CLI
.
You can check the currently active account by running
gcloud auth list
.
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"
PowerShell (Windows)
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:
Response
{
"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 "
}
Disable PITR
Console
In the Google Cloud console, go to the Cloud SQL Instances page.
Go to Cloud SQL Instances
Open the more actions menu for the instance you
want to disable and select Edit .
Under Customize your instance , expand the Data Protection
section.
Clear Enable point-in-time recovery .
Click Save .
In the Overview page for the instance, under
Configuration , the PITR setting is listed as
disabled.
gcloud
Disable point-in-time recovery:
gcloud sql instances patch INSTANCE_NAME \
--no-enable-point-in-time-recovery
Confirm your change:
gcloud sql instances describe INSTANCE_NAME
In the backupConfiguration
section, you see
pointInTimeRecoveryEnabled: false
if the change was successful.
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:
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:
curl (Linux, macOS, or Cloud Shell)
Note:
The following command assumes that you have logged in to
the gcloud
CLI with your user account by running
gcloud init
or
gcloud auth login
, or by using Cloud Shell ,
which automatically logs you into the gcloud
CLI
.
You can check the currently active account by running
gcloud auth list
.
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 "
PowerShell (Windows)
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:
Response
{
"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 "
}
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:
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:
curl (Linux, macOS, or Cloud Shell)
Note:
The following command assumes that you have logged in to
the gcloud
CLI with your user account by running
gcloud init
or
gcloud auth login
, or by using Cloud Shell ,
which automatically logs you into the gcloud
CLI
.
You can check the currently active account by running
gcloud auth list
.
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 "
PowerShell (Windows)
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:
Response
{
"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 "
}
Check the storage location of transaction logs used for PITR
You can check where your Cloud SQL instance is storing the transaction
logs used for PITR.
gcloud
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.
In the output of the command, the transactionalLogStorageState
field provides information about where the transaction
logs for PITR are stored for the instance.
The returned transaction log storage location is DISK
.
SQL Server instances can store the transaction logs used for PITR only on disk.
Set transaction log retention
To set the number of days to retain transaction logs:
Console
In the Google Cloud console, go to the Cloud SQL Instances page.
Go to Cloud SQL Instances
Open the more actions menu for the instance you
want to set the transaction log on and select Edit .
Under Customize your instance , expand the Data Protection
section.
In the Enable point-in-time recovery section, expand
Advanced options .
Enter the number of days to retain logs, from 1-7.
Click Save .
gcloud
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. The valid range is between 1 and 7. The default value is 7
if not specified. Only valid when point in time recovery is enabled.
Keeping more days of transaction logs requires bigger storage
size.
gcloud sql instances patch INSTANCE-NAME \
--retained-transaction-log-days=DAYS-TO-RETAIN
REST v1
Before using any of the request data,
make the following replacements:
days-to-retain : The number of days to retain transaction logs, from 1 to 7
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":
{
"transactionLogRetentionDays": "days-to-retain "
}
}
}
To send your request, expand one of these options:
curl (Linux, macOS, or Cloud Shell)
Note:
The following command assumes that you have logged in to
the gcloud
CLI with your user account by running
gcloud init
or
gcloud auth login
, or by using Cloud Shell ,
which automatically logs you into the gcloud
CLI
.
You can check the currently active account by running
gcloud auth list
.
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 "
PowerShell (Windows)
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:
Response
{
"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 "
}
REST v1beta4
Before using any of the request data,
make the following replacements:
days-to-retain : The number of days to retain transaction logs, from 1 to 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":
{
"transactionLogRetentionDays": "days-to-retain "
}
}
}
To send your request, expand one of these options:
curl (Linux, macOS, or Cloud Shell)
Note:
The following command assumes that you have logged in to
the gcloud
CLI with your user account by running
gcloud init
or
gcloud auth login
, or by using Cloud Shell ,
which automatically logs you into the gcloud
CLI
.
You can check the currently active account by running
gcloud auth list
.
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 "
PowerShell (Windows)
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:
Response
{
"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 "
}
What's next