When a production database goes down – whether from a bad migration, an accidental DROP TABLE, or a rogue script – the clock starts ticking. Every minute of downtime is lost revenue, broken trust, and a very stressful Slack channel.
This post walks through how Google Cloud SQL’s backup and recovery features work, common disaster scenarios, and the recovery playbook a developer should follow for each. The examples use a typical SaaS application backed by PostgreSQL on Cloud SQL, but the principles apply broadly.
Cloud SQL Backup Fundamentals
Before anything goes wrong, you need to understand what Cloud SQL gives you out of the box and what you need to configure yourself.
Automated Backups
Cloud SQL can take daily automated backups of your instance. These are full snapshots of the entire database and are retained for a configurable window (default 7 days, max 365).
# gcloud: verify automated backups are enabled
gcloud sql instances describe my-instance \
--format="value(settings.backupConfiguration)"
Key settings to configure:
| Setting | Recommendation | Why |
|---|---|---|
backupConfiguration.enabled | true | Non-negotiable for production |
backupConfiguration.startTime | Off-peak hours (e.g. 04:00 UTC) | Minimizes performance impact |
backupConfiguration.backupRetentionSettings.retainedBackups | 14-30 | Gives you a wider recovery window |
backupConfiguration.pointInTimeRecoveryEnabled | true | Enables PITR (see below) |
backupConfiguration.transactionLogRetentionDays | 7 | How far back PITR can reach |
Point-in-Time Recovery (PITR)
Automated backups give you daily snapshots. PITR fills the gaps by continuously archiving write-ahead logs (WAL for PostgreSQL, binary logs for MySQL). This lets you restore to any second within the retention window — not just to the time of the last backup.
# Enable PITR on an existing instance
gcloud sql instances patch my-instance \
--enable-point-in-time-recovery \
--retained-transaction-log-days=7
PITR is the single most important setting for disaster recovery. Without it, you lose every write between your last automated backup and the incident.
On-Demand Backups
You can trigger a backup manually before risky operations:
gcloud sql backups create --instance=my-instance \
--description="pre-migration-backup-2026-04-08"
Rule of thumb: always take an on-demand backup before running migrations, bulk data operations, or any ad-hoc SQL against production.
Disaster Scenarios and Recovery Playbooks
Scenario 1: Accidental Table Drop or Data Deletion
What happened: A developer ran a DROP TABLE or DELETE FROM without a WHERE clause against production. Maybe it was a script meant for staging. Maybe an AI-generated SQL statement was executed without review.
Impact: One or more tables are gone or empty. The application is throwing 500s.
Recovery options:
Option A: PITR (best if available)
Restore to the moment just before the destructive command. You’ll need the approximate timestamp.
# Restore to a clone instance first — never restore directly over production
gcloud sql instances clone my-instance my-instance-recovery \
--point-in-time="2026-04-08T10:59:00Z"
This creates a new instance with the database state at that exact second. You can then:
- Verify the data on the clone
- Export the affected tables from the clone
- Import them back into the production instance
# Export a specific table from the recovery clone
gcloud sql export sql my-instance-recovery gs://my-bucket/recovery/users-table.sql \
--database=myapp_production \
--table=users
# Import into production
gcloud sql import sql my-instance gs://my-bucket/recovery/users-table.sql \
--database=myapp_production
Option B: Restore from automated backup
If PITR is not enabled, restore the most recent automated backup that predates the incident.
# List available backups
gcloud sql backups list --instance=my-instance
# Restore a specific backup (this overwrites the instance)
gcloud sql backups restore BACKUP_ID --restore-instance=my-instance
Warning: Restoring a backup directly onto your production instance overwrites everything. All writes since that backup are lost. Prefer cloning to a recovery instance first.
The data gap problem:
When you restore from a backup taken at, say, 4:00 AM, but the incident happened at 11:00 AM, you lose 7 hours of data. This is the gap you’ll need to address manually. Common strategies:
- Application-level event logs: If your app publishes events to a message queue (Kafka, Pub/Sub), you can replay them.
- Analytics replicas: If you replicate data to BigQuery, Snowflake, or another analytics store, you can query the missing records from there and re-import them.
- Audit tables: If your application logs changes to an audit table in a separate database, those records survive.
-- Example: querying BigQuery for records created during the gap window
SELECT *
FROM `project.dataset.user_actions`
WHERE created_at BETWEEN TIMESTAMP('2026-04-08 04:00:00', 'America/Vancouver')
AND TIMESTAMP('2026-04-08 11:00:00', 'America/Vancouver')
AND action_type = 'account_status_change'
You then re-ingest these records into production, typically via a script run in your application’s console or through a migration task.
Scenario 2: Interrupted Background Job
What happened: A critical scheduled job — say, one that generates weekly records for all active users — was running when the incident occurred. The database was restored from backup, but the job was killed mid-execution. Some users got their records; others didn’t.
Impact: No application errors (the data that exists is valid), but there’s a silent gap. Some users are missing records they should have.
Recovery playbook:
Step 1 — Quantify the gap
Before doing anything, measure what’s missing:
# Find users who should have a record but don't
target_date = Date.parse('2026-05-30')
users_missing = User.where(status: ['active', 'subscribed'])
.where.not(id: WeeklyRecord.where(week_date: target_date).select(:user_id))
users_missing.count
Record the count. You’ll need it for verification later.
Step 2 – Understand the generation logic
Before re-running anything, understand what the job does:
- Does it check for existing records before creating? (idempotent?)
- Does it behave differently based on user status? (e.g., suspended users get a different treatment)
- Does it trigger side effects? (emails, webhooks, billing)
If the job is idempotent — meaning running it twice for the same user produces the same result without duplicates — you can safely re-run it for all users, not just the ones missing records. This is simpler and safer than trying to target only the gap.
Step 3 – Re-run with guardrails
Write a targeted script rather than re-triggering the entire job:
target_date = Date.parse('2026-05-30')# Pre-checkbaseline_count = WeeklyRecord.where(week_date: target_date).countputs "Records before: #{baseline_count}"# Find and process missing usersusers_missing = User.where(status: ['active', 'subscribed']) .where.not(id: WeeklyRecord.where(week_date: target_date).select(:user_id))puts "Users missing records: #{users_missing.count}"users_missing.find_each do |user| WeeklyRecordGenerator.new(user).generate(target_date)rescue => e puts "Failed for User ##{user.id}: #{e.message}"end# Post-checknew_count = WeeklyRecord.where(week_date: target_date).countputs "Records after: #{new_count}"puts "Delta: #{new_count - baseline_count}"
Step 4 – Verify
Check that:
- The record count increased by the expected amount
- No duplicates were created
- No users are still missing records
- Any status-dependent logic was applied correctly (e.g., suspended users got the right treatment)
Scenario 3: Corrupted Data from a Bad Migration
What happened: A migration altered a column type, dropped a constraint, or backfilled data incorrectly. The application is running but producing wrong results.
Impact: Data is present but incorrect. This is often harder to detect than missing data.
Recovery playbook:
- Don’t panic-restore. If the app is functional (just producing wrong data), you have time to assess.
- Clone to a recovery instance from a backup predating the migration:
gcloud sql instances clone my-instance pre-migration-clone \ --point-in-time="2026-04-07T23:00:00Z" - Diff the data between production and the clone to understand exactly what changed:
-- Compare row counts SELECT 'production' as source, count(*) FROM production.orders UNION ALL SELECT 'backup' as source, count(*) FROM backup_clone.orders; -- Find rows that differ SELECT p.id, p.amount as prod_amount, b.amount as backup_amount FROM production.orders p JOIN backup_clone.orders b ON p.id = b.id WHERE p.amount != b.amount; - Write a targeted fix rather than a full restore (which would lose post-migration legitimate writes).
- Write a rollback migration if the schema change itself was the problem.
Scenario 4: Full Instance Failure
What happened: The Cloud SQL instance is unreachable – maybe a zone outage, maybe accidental instance deletion.
Recovery options:
If the instance still exists (zone outage):
Cloud SQL instances configured for high availability will automatically failover to a standby in another zone. If you don’t have HA enabled:
# Enable HA (requires instance restart)gcloud sql instances patch my-instance --availability-type=REGIONAL
If the instance was deleted:
Deleted instances can be recovered within a limited window if deletion protection wasn’t bypassed:
# Enable deletion protectiongcloud sql instances patch my-instance --deletion-protection
If truly gone, restore from the most recent backup to a new instance:
gcloud sql instances create my-instance-restored \ --source-backup=BACKUP_ID \ --tier=db-custom-4-16384 \ --region=us-west1
Then update your application’s database connection string to point to the new instance.
Prevention Checklist
The best disaster recovery is the one you never need. Here’s what to set up before things go wrong:
Cloud SQL Configuration
# The production-ready configuration checklistgcloud sql instances patch my-instance \ --backup-start-time=04:00 \ --enable-point-in-time-recovery \ --retained-transaction-log-days=7 \ --retained-backups-count=30 \ --deletion-protection \ --availability-type=REGIONAL
Operational Practices
1. Never run ad-hoc SQL directly against production
Use a read replica for investigative queries. If you must write, use a transaction with a manual ROLLBACK checkpoint:
BEGIN;
-- Your change here
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';
-- Verify before committing
SELECT count(*) FROM users WHERE status = 'inactive';
-- Only if the count looks right:
COMMIT;
-- Otherwise:
ROLLBACK;
2. Take on-demand backups before risky operations
gcloud sql backups create --instance=my-instance \ --description="pre-bulk-update-$(date +%Y%m%d-%H%M%S)"
3. Review AI-generated SQL before executing
AI tools are excellent at generating SQL, but they don’t understand your data invariants. A syntactically correct DROP TABLE or DELETE without a WHERE clause is still catastrophic. Always:
- Read the generated SQL line by line
- Run it on staging first
- Wrap destructive operations in a transaction
- Have a second pair of eyes for DDL changes
4. Maintain an analytics replica
Replicate critical tables to BigQuery or another analytics store. This serves as both an analytics platform and a recovery source. If your primary database loses data, you can query the replica for the gap window and re-ingest.
# Set up a BigQuery data transfer from Cloud SQLbq mk --transfer_config \ --target_dataset=sql_replica \ --display_name="Production SQL Replica" \ --data_source=scheduled_query \ --schedule="every 1 hours"
5. Use IAM to restrict destructive operations
Not every developer needs cloudsql.instances.delete or direct SQL access to production:
# Create a read-only role for most developersgcloud projects add-iam-policy-binding my-project \ --member="group:developers@company.com" \ --role="roles/cloudsql.viewer"# Grant write access only to the ops teamgcloud projects add-iam-policy-binding my-project \ --member="group:database-ops@company.com" \ --role="roles/cloudsql.admin"
The Recovery Timeline: What Happens in Practice
Here’s what a real recovery typically looks like, end to end:
T+0min Incident detected (alerts fire, app errors spike)T+5min Confirm the issue — is it a code bug or data loss?T+10min Identify the last good backup / PITR targetT+15min Clone instance from backup (takes 5-30 min depending on size)T+45min Verify restored data on the cloneT+60min Restore production from clone or selectively import tablesT+90min Identify the data gap (writes between backup and incident)T+120min Query analytics replica / event logs for gap dataT+150min Re-ingest gap data, verify countsT+180min Re-run interrupted jobs with verificationT+210min Final validation — all counts match, no duplicates, app healthyT+240min Post-incident review
The total time depends on database size, gap complexity, and whether you had PITR enabled. With PITR, the gap is seconds. Without it, you could be looking at hours of manual data reconciliation.
Key Takeaways
- Enable PITR. It’s the difference between losing seconds of data and losing hours.
- Always clone to a recovery instance first. Never restore directly over production unless you have no other option.
- Maintain an analytics replica. It’s your insurance policy for the data gap.
- Quantify before you fix. Record counts before and after every recovery step. You can’t verify what you didn’t measure.
- Understand your jobs’ idempotency. If a background job was interrupted, knowing whether it’s safe to re-run is the difference between a smooth recovery and creating a bigger mess.
- Take on-demand backups before risky operations. The 30 seconds it takes could save you 4 hours of recovery.
- Review all SQL before execution. Especially AI-generated SQL. Trust, but verify.
Production incidents are stressful, but with the right configuration and a clear playbook, they don’t have to be catastrophic. Set up your backups today — future you will be grateful.
Happy fixing!
The Rails Drop 