This page describes how backups of your Cloud SQL instance work. You can perform backups on your primary instance.
For step-by-step directions for scheduling or managing backups, see Create and manage on-demand and automatic backups.
For an overview of how to restore data to an instance from the backup, see Overview of restoring an instance.
What backups provide
Backups help you restore lost data to your Cloud SQL instance. Additionally, if an instance is having a problem, you can restore it to a previous state by using the backup to overwrite it. Enable automated backups for any instance that contains necessary data. Backups protect your data from loss or damage.
What backups cost
By default, Cloud SQL retains 7 automated backups , in addition to on-demand backups. You can configure how many automated backups to retain (from 1 to 365). We charge a lower rate for backup storage than for other types of instances.
You can retain more, but not less than seven automated backups.As part of deleting an instance, you can take a final backup of your data. This way, you can recreate any instances that you delete. However, if you don't take a final backup, then after you delete an instance, Cloud SQL deletes all backups. For more information, see Recovery backups.
See the pricing page for more information.
Backups versus exports
Backups are managed by Cloud SQL according to retention policies, and are stored separately from the Cloud SQL instance. Cloud SQL backups differ from an export uploaded to Cloud Storage, where you manage the lifecycle. Backups encompass the entire database. Exports can select specific contents.
Backup and restore operations can't be used to upgrade a database to a later version. You can only restore from a backup to an instance with the same database version.
To upgrade to a later version, you can export and then import your database to a new Cloud SQL instance.About the backup size
Cloud SQL backups are incremental. They contain only data that changed after the previous backup was taken. Your oldest backup is a similar size to your database, but the sizes of subsequent backups depend on the rate of change of your data. When the oldest backup is deleted, the size of the next oldest backup increases so that a full backup still exists.
You can check the size of an individual backup. The backup size represents the billable size for each backup.
Types of backups
Cloud SQL performs three types of backups:
On-demand backups
You can create a backup at any time. This could be useful if you are about to perform a risky operation on your database, or if you need a backup and you do not want to wait for the backup window. You can create on-demand backups for any instance, whether the instance has automatic backups enabled or not.
On-demand backups are not automatically deleted the way automated backups are. They persist until you delete them or until their instance is deleted. Because they are not automatically deleted, on-demand backups can have a long-term effect on your billing charges.
Automated backups
Automated backups are taken daily, within a 4-hour backup window. The backup starts during the backup window. When possible, schedule backups when your instance has the least activity.
We recommend that you don't delete any automated backups because they're needed to support point-in-time recovery.
During the backup window, automated backups occur every day your instance is running. One additional automated backup is taken after your instance is stopped to safeguard all changes prior to the instance stopping. Up to seven most recent backups are retained, by default. You can configure how many automated backups to retain, but you can't retain fewer than the default (seven).
Final backups
Final backups allow you to take a backup of your Cloud SQL instance before you delete the instance. This is useful to retain the instance data after you delete the instance. You can use the final backup later to either create an instance or to restore to an existing instance. For more information about accessing and viewing details about your final backup, see View a list of final backups.
By default, Cloud SQL retains the final backup for 30 days. However, you can customize how long Cloud SQL retains the backup, from 1 day to 365 days. You can then restore the instance from the backup as long as it's available. Final backups are charged similar to other backups for the number of days retained.
Unlike automated backups and on-demand backups, which are associated with an instance and are available only when the instance exists, you can view and use final backups for restore operations after Cloud SQL deletes the instance.
Retained backups
Retained backups are backups that are retained by Cloud SQL after an instance is deleted. These backups consist of on-demand backups and automated backups created when the instance was live. When you delete an instance, these backups become independent of your instance and are stored at the project level. Retained backups are different from final backups, which are the last backups taken at time of instance deletion.
You can update the description of these backups to make it easier to manage them in your Google Cloud project. Retained backups can be restored to a new or existing Cloud SQL instance at any time.
For these backups, the retention period is defined by the type of backup it is and can't be changed after the instance is deleted. On-demand backups are kept indefinitely until either the backup is manually deleted, or the project containing the backup is deleted. Automated backups are deleted on a rolling basis, one backup per day, after the instance is deleted. The rolling period is defined based on the retention setting of the instance prior to deletion. For example, if your instance's automated backup retention setting was set to 7, then the latest automated backup is deleted 7 days after the instance deletion.
Retained backups can be deleted manually at any time. However, when you delete a retained backup, the deleted backups can't be recovered.
Since instance names can be used after an instance is deleted in Cloud SQL,
retained backups are stored in your Google Cloud project with a field
called instance_deletion_time. This field allows you to identify whether
a particular backup belongs to a live or deleted instance. You can also update
the description of a backup to make it easier to manage them.
For more information on how to enable retained backups for your new or existing instances, see Manage retained backups. For more information on how to restore an instance from a retained backup, see Restore from a retained backup.
Where backups are stored
Backups locations include:
- Default locations that Cloud SQL selects, based on the location of the original instance.
- Custom locations that you choose when you do not want to use the default location.
Default backup locations
If you do not specify a storage location, your backups are stored in the multiregion that is
geographically closest to the location of your Cloud SQL instance. For example, if your
Cloud SQL instance is in us-central1, your backups are stored in the
us multi-region by default. However, a default location like
australia-southeast1 is outside of a multi-region. The closest multi-region is
asia.
Custom backup locations
Cloud SQL lets you select a custom location for your backup data. This is useful if your organization needs to comply with data residency regulations that require you to keep your backups within a specific geographic boundary. If your organization has this type of requirement, it probably uses a Resource Location Restriction organizational policy. With this policy, when you try to use a geographic location that does not comply with the policy, you see an alert on the Backups page. If you see this alert, you need to change the backup location to a location the policy allows.
When selecting a custom location for a backup, consider the following:
- Cost: one cluster in your instance may be in a lower-cost region than the others.
- Proximity to your application server: you might want to store the backup as close to your serving application as possible.
- Storage utilization: you need enough storage space to keep your backup as it grows in size. Depending on your workload, you might have clusters of different sizes or with different disk usages. This might factor into which cluster you choose.
For a complete list of valid regional values, see Instance Locations. For a complete list of multi-regional values, see Multi-regional locations.
For more information about setting locations for backups and seeing the locations of backups taken for an instance, see Set a custom location for backups and View backup locations.
Automated backup retention
Automated backup retention can be set to more, but not less than the default (seven).
See Setting automated backup retention.
Can I export a backup?
No, you can't export a backup. You can only export instance data. See Exporting data from Cloud SQL.
About the special backup user
Cloud SQL creates a special database user, cloudsqladmin, for each
instance, and generates a unique instance-specific password for it.
Cloud SQL logs in as the cloudsqladmin user to perform automated backups.
How backups affect instance operations
Writes and other operations are unaffected by backup operations.
Backup rate limitations
Cloud SQL limits the rate for backup operations on the data disk. You are allowed a maximum of five backup operations every 50 minutes per instance per project. If a backup operation fails, it does not count towards this quota. If you reach the limit, the operation fails with an error message that tells you when you can retry.
Let's take a look at how Cloud SQL performs rate limiting for backups.
Cloud SQL uses tokens from a bucket to determine how many backup operations are available at any one time. Each instance has a bucket. There's a maximum of five tokens in the bucket that you can use for backup operations. Every 10 minutes, a new token is added to the bucket. If the bucket is full, the token overflows.
Each time you issue a backup operation, a token is granted from the bucket. If the operation succeeds, the token is removed from the bucket. If it fails, the token is returned to the bucket. The following diagram shows how this works:
Backup and data integrity checks
Cloud SQL performs background database integrity checks automatically to identify any potential data integrity issues. These checks are done as offline processes by restoring a sampling of customer-initiated backups or recovery backups.
Recovery backups
Before you delete an instance, you can take a final backup of your data. You can also enable retained backups prior to deleting your instance to retain all automatic and on-demand backups. For more information, see Manage retained backups.
You can restore from a retained or final backup to a new instance, an existing instance, an instance in a different project, or a new instance in another region. For more information, see Restore an instance.
Cloud SQL also attempts to retain at least one last good daily backup of every active instance, if there are no good backups available as part of the automated backup policy. This backup can be used for recovery purposes by contacting Google Cloud Customer Care.
Troubleshooting
| Issue | Troubleshooting |
|---|---|
| You can't see the current operation's status. | The Google Cloud console reports only success or failure when the operation
is done. It isn't designed to show warnings or other updates.
Run the
|
| You want to find out who issued an on-demand backup operation. | The user interface doesn't show the user who started an operation.
Look in the logs and filter by text to find the user. You may need to use audit logs for private information. Relevant log files include:
|
| After an instance is deleted, you can't take a backup of the instance. | If you delete an instance without taking a final backup of the data, then no data recovery is possible. However, if you restore the instance, then Cloud SQL also restores the backups. For more information on recovering a deleted instance, see Recovery backups. If you have done an export operation, create a new instance and then do an import operation to recreate the database. Exports are written to Cloud Storage and imports are read from there. |
| An automated backup is stuck for many hours and can't be canceled. | Backups can take a long time depending on the database size.
If you really need to cancel the operation, you can ask
customer support to |
| A restore operation can fail when one or more users referenced in the SQL dump file don't exist. | Before restoring a SQL dump, all the database users who own objects or
were granted permissions on objects in the dumped database must exist in the
target database. If they don't, the restore operation fails to recreate the
objects with the original ownership or permissions.
Create the database users before restoring the SQL dump. |
| You want to increase the number of days that you can keep automatic backups from seven to 30 days, or longer. | You can
configure the number of automated backups to retain, but you can't retain fewer than the default (seven). Automated backups get pruned
regularly based on the retention value configured. Unfortunately, this means that the
currently visible backups are the only automated backups you can restore from.
To keep backups indefinitely, you can create an on-demand backup, as they are not deleted in the same way as automated backups. On-demand backups remain indefinitely. That is, they remain until they're deleted or the instance they belong to is deleted. Because that type of backup is not deleted automatically, it can affect billing. |
| An automated backup failed and you didn't receive an email notification. | To have Cloud SQL notify you of the backup's status, configure a log-based alert. |
| You're unable to restore your instance using the Transact-SQL RESTORE command or the SQL Server Management Studio (SSMS). |
Cloud SQL does not support restoring instances through SSMS.
To restore your instance, run the
gcloud sql import command.
|