How to Manage Cloud SQL Backups and Disaster Recovery: A Complete Guide

Cloud SQL provides automated daily backups, on-demand snapshots, point-in-time recovery, and high-availability replicas to protect your data and minimize downtime.

Cloud SQL is a fully-managed relational database service that layers multiple backup and disaster-recovery (DR) mechanisms. According to the google/skills repository, you can implement robust data protection strategies using native features in the Enterprise and Enterprise Plus editions. This guide covers all backup types, recovery methods, and infrastructure-as-code configurations.

---

Automated and On-Demand Backups

Cloud SQL offers two primary backup mechanisms that serve different operational needs.

Automated Daily Backups

Cloud SQL takes a snapshot of the entire instance during a user-configurable 4-hour window each day. Retention varies by edition:

• Enterprise: Up to 7 days of automated backups
• Enterprise Plus: Up to 35 days of automated backups

You configure this in gcloud sql instances patch or Terraform's backup_configuration block. As shown in [dr-backups.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/dr-backups.md#L5-L9), the backup window ensures minimal production impact by running during off-peak hours.

On-Demand Backups

Create a backup at any moment—ideal before schema migrations or major updates. Unlike automated backups, on-demand backups persist until you manually delete them. The [dr-backups.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/dr-backups.md#L9-L10) reference notes this gives you explicit control over critical restore points.

---

Enhanced Backups with Backup and DR Service

Enterprise Plus customers can delegate backup storage to the centralized Backup and Disaster Recovery Service. This provides capabilities documented in [dr-backups.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/dr-backups.md#L11-L15):

• Air-gapped, immutable vaults for ransomware protection
• Extended retention up to 10 years with flexible schedules (hourly to yearly)
• Retention Lock to enforce non-deletable policies
• Cross-project recovery—restore databases into different GCP projects for isolated recovery environments and compliance segregation

---

Point-in-Time Recovery (PITR)

PITR enables restoring an instance to any second within your retention window using continuous transaction logging:

• MySQL: Binary logs
• PostgreSQL: Write-ahead logs (WAL)
• SQL Server: Transaction logs

Retention windows match your edition: 7 days for Enterprise, 35 days for Enterprise Plus. As implemented in [dr-backups.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/dr-backups.md#L17-L19), PITR fills the gap between full backups, minimizing potential data loss to seconds rather than hours.

---

High Availability and Disaster Recovery Architectures

Cloud SQL layers multiple replication strategies for different recovery objectives.

HA Standby Replicas

A regional standby VM provides instant failover during zonal failures. The standby maintains identical data consistency to the primary, as described in [dr-backups.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/dr-backups.md#L39-L40). Enable this with availability_type = "REGIONAL" in Terraform or --availability-type=REGIONAL in gcloud.

Read Replicas for Scale and Failover

Standalone read replicas serve two purposes:

1. Scale read traffic—zonal or cross-region replicas offload query load
2. Emergency promotion—promote any replica to primary if the original fails

As noted in [dr-backups.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/dr-backups.md#L41-L44), this requires application reconfiguration but provides flexible recovery options.

Enterprise Plus DR Replicas

Enterprise Plus introduces designated cross-region DR replicas with enhanced capabilities documented in [dr-backups.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/dr-backups.md#L48-L55):

Feature	DR Replica
Fast cross-region failover	Automatic promotion with minimal RTO
Zero-downtime switchover	Planned maintenance without application disruption
RPO target	Typically under 1 minute

---

Managing Backups via CLI and IaC

All backup, PITR, HA, and DR operations are exposed through gcloud sql commands and Terraform resources, as cataloged in [iac-usage.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/iac-usage.md#L30-L33).

Essential gcloud Commands

# Enable automated daily backups and PITR (4am window)

gcloud sql instances patch my-instance \
  --backup-start-time=04:00 \
  --enable-point-in-time-recovery \
  --quiet

# Create an on-demand backup before a migration

gcloud sql backups create --instance=my-instance \
  --description="pre-migration snapshot" \
  --quiet

# List existing backups

gcloud sql backups list --instance=my-instance --quiet

# Restore a backup onto the same instance (overwrites current data)

gcloud sql backups restore BACKUP_ID \
  --restore-instance=my-instance \
  --quiet

# Restore a backup onto a new target instance

gcloud sql backups restore BACKUP_ID \
  --restore-instance=target-instance \
  --backup-instance=my-instance \
  --quiet

# Perform a PITR clone to a specific timestamp

gcloud sql instances clone my-instance restored-instance \
  --point-in-time="2026-07-16T12:00:00Z" \
  --quiet

# Trigger an HA failover (test standby replica)

gcloud sql instances failover my-ha-instance --quiet

# Switchover to a DR replica (Enterprise Plus only)

gcloud sql instances switchover my-dr-replica --quiet

Terraform Configuration

resource "google_sql_database_instance" "my_instance" {
  name             = "my-instance"
  database_version = "POSTGRES_18"
  region           = "us-central1"

  settings {
    tier = "db-custom-2-7680"
    backup_configuration {
      enabled            = true
      start_time         = "04:00"
      point_in_time_recovery_enabled = true
    }
    availability_type = "REGIONAL"   # HA standby

  }
}

---

Choosing Your Backup and DR Strategy

Objective	Recommended Configuration	RPO	RTO
Basic protection	Automated daily backups + PITR	< 24 hours	Hours
Zonal resilience	Automated backups + HA standby	< 1 second	< 60 seconds
Regional resilience	HA + cross-region read replica	Minutes	Minutes-hours
Enterprise DR	HA + Enterprise Plus DR replica	< 1 minute	< 1 minute

---

Summary

• Automated backups run daily with 7-day (Enterprise) or 35-day (Enterprise Plus) retention—configure via backup-start-time
• On-demand backups persist until deleted, perfect for migration milestones
• PITR uses continuous transaction logs to achieve second-level recovery points
• HA standby replicas eliminate zonal single points of failure with automatic failover
• Enterprise Plus DR replicas provide cross-region disaster recovery with sub-minute RPO/RTO
• Backup and DR Service adds immutable vaults, decade-long retention, and cross-project recovery for compliance-heavy workloads
• All operations are scriptable via gcloud sql commands and Terraform resources per the google/skills reference implementations

---

Frequently Asked Questions

What is the difference between a failover and a switchover in Cloud SQL?

A failover is an unplanned, automatic promotion of the HA standby or DR replica when the primary becomes unhealthy—triggered by the system or manually via gcloud sql instances failover. A switchover is a planned, zero-downtime operation using gcloud sql instances switchover that gracefully demotes the primary and promotes the DR replica, typically for maintenance windows. Switchover is available only with Enterprise Plus DR replicas.

How does Point-in-Time Recovery handle binary log expiration?

PITR retention is bounded by your edition's backup retention window—7 or 35 days. Transaction logs older than this window are purged automatically, so you cannot restore beyond that point using PITR. For longer-term recovery, use enhanced backups with the Backup and DR Service, which supports retention up to 10 years.

Can I restore a Cloud SQL backup to a different database version?

No. Cloud SQL restores require the target instance to use the same database version as the source at backup time. For version upgrades, restore to a same-version instance, then perform an in-place upgrade. The [cli-usage.md](https://github.com/google/skills/blob/main/skills/cloud/cloud-sql-basics/references/cli-usage.md) reference documents version compatibility requirements.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}