RDS Backup Restore Testing: 7 Checks Before You Trust It

#devops

Originally published on kuryzhev.cloud

Your RDS backups show "available" every night in the console. Green checkmark, no alarms, everyone's happy. But have you actually restored one of those snapshots and queried the data inside it? I ask because we didn't — for almost a year — until a corrupted binlog turned our "reliable" backup strategy into a three-hour incident call. RDS backup restore testing isn't optional infrastructure hygiene, it's the only way to know your recovery plan actually works.

Automate the Restore, Don't Trust the "Available" Status

An RDS snapshot marked "available" only confirms the backup process finished without error — it says nothing about whether the data inside is usable. I've seen teams treat a green CloudWatch backup alarm as proof of recoverability, then discover during a real incident that the snapshot restores into an instance with half the expected rows. The fix is boring but effective: schedule a recurring EventBridge rule that triggers a Lambda to run restore-db-instance-from-db-snapshot weekly or monthly, automatically, without a human remembering to do it.

This isn't about paranoia — it's about closing the gap between "backup completed" and "backup is recoverable." Those are two very different claims, and RDS only ever promises you the first one.

Restore Into an Isolated Network, Not Your Production VPC

Restoring a snapshot into the same VPC as production is asking for CIDR and security-group conflicts, and in the worst case, test traffic touching real systems. We keep a dedicated "restore-testing" subnet group with zero routes to prod app servers, and tag every restored instance with Purpose=restore-test plus an expiry timestamp so cleanup automation can find it later.

Watch out: if you forget to attach a security group during the restore call, the instance comes up "available" but is completely unreachable — your validation script will just hang or fail silently with a connection timeout, and it's easy to mistake that for a data problem instead of a networking one.

Verify Point-in-Time Recovery, Not Just Snapshot Age

Snapshots are simple — PITR is not. Point-in-time recovery depends on continuous transaction log shipping, and that pipeline fails silently far more often than a nightly snapshot job does. Before you assume PITR can restore to "now," check LatestRestorableTime:

$ aws rds describe-db-instances \
    --db-instance-identifier prod-orders-db \
    --query 'DBInstances[0].[LatestRestorableTime,EarliestRestorableTime]' \
    --output table

Output looks like this:

-----------------------------------------------
|            DescribeDBInstances              |
+-----------------------------+---------------+
|  2024-05-14T09:42:17.000Z  | 2024-04-09T00:00:00.000Z |
+-----------------------------+---------------+

# Gotcha: if LatestRestorableTime is more than 5-10 minutes behind current
# time, binlog retention or transaction log backup may be misconfigured —
# investigate before trusting PITR for RTO planning.

For MySQL/MariaDB, PITR quietly depends on binlog retention configured via mysql.rds_set_configuration('binlog retention hours', 168). If that's not set, your recoverable window shrinks without any alarm firing. And don't confuse Aurora's "backtrack" feature with PITR — backtrack tops out at 72 hours and only works on Aurora MySQL, it's not a substitute for transaction-log-based recovery.

Validate Data Integrity After Restore, Not Just Connectivity

A restored instance that accepts connections isn't the same as a restored instance with intact data. I've watched a "successful" restore return a working psql prompt against a table that was silently truncated three days before the incident — connectivity checks alone would've called that a pass. Run row-count comparisons and checksum queries against known baselines: CHECKSUM TABLE for MySQL, pg_checksums --check for Postgres (note: Postgres checksums have to be enabled at initdb time, you can't bolt them on retroactively).

Here's a script we run after every scheduled restore test — it restores, waits, checks row counts against a known minimum, then tears itself down:

#!/usr/bin/env bash
# restore-test.sh - automate an RDS snapshot restore + basic validation
set -euo pipefail

SNAPSHOT_ID="prod-db-snapshot-2024-05-01"
TEST_INSTANCE_ID="restore-test-$(date +%s)"
SUBNET_GROUP="restore-testing-subnet-group"
SECURITY_GROUP="sg-0123456789abcdef0"

echo "Starting restore of $SNAPSHOT_ID into $TEST_INSTANCE_ID..."

aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier "$TEST_INSTANCE_ID" \
  --db-snapshot-identifier "$SNAPSHOT_ID" \
  --db-subnet-group-name "$SUBNET_GROUP" \
  --vpc-security-group-ids "$SECURITY_GROUP" \
  --no-publicly-accessible \
  --tags Key=Purpose,Value=restore-test Key=ExpiryDate,Value="$(date -d '+1 day' +%F)"

echo "Waiting for instance to become available..."
aws rds wait db-instance-available --db-instance-identifier "$TEST_INSTANCE_ID"

ENDPOINT=$(aws rds describe-db-instances \
  --db-instance-identifier "$TEST_INSTANCE_ID" \
  --query 'DBInstances[0].Endpoint.Address' --output text)

echo "Instance available at $ENDPOINT. Running validation checks..."

# Basic connectivity + row count check (Postgres example)
ROW_COUNT=$(PGPASSWORD="$DB_PASSWORD" psql -h "$ENDPOINT" -U app_readonly -d appdb -t -c \
  "SELECT count(*) FROM orders;")

EXPECTED_MIN_ROWS=100000

if [ "$ROW_COUNT" -lt "$EXPECTED_MIN_ROWS" ]; then
  echo "FAIL: row count $ROW_COUNT below expected minimum $EXPECTED_MIN_ROWS"
  exit 1
fi

echo "PASS: row count check ($ROW_COUNT rows)"

# Cleanup - delete test instance after validation
aws rds delete-db-instance \
  --db-instance-identifier "$TEST_INSTANCE_ID" \
  --skip-final-snapshot

echo "Restore test complete. Test instance deleted."

Wire this into your CI runner or a Lambda triggered by EventBridge, and you get an automated proof-of-recoverability report instead of a green dashboard icon that means nothing.

Test Cross-Region and Cross-Account Snapshot Copies for DR

If your disaster recovery plan only tests same-region restores, it isn't really tested — a regional outage is exactly the scenario your DR plan exists for, and that's the one path most teams never exercise. Cross-region snapshot copies require re-encryption with a KMS key that exists in the destination region, and this trips people up constantly because the source key ARN simply doesn't resolve there.

aws rds copy-db-snapshot \
  --source-region us-east-1 \
  --source-db-snapshot-identifier arn:aws:rds:us-east-1:111122223333:snapshot:prod-orders-2024-05-01 \
  --target-db-snapshot-identifier prod-orders-dr-copy \
  --kms-key-id arn:aws:kms:us-west-2:111122223333:key/abcd-1234-efgh-5678

Confirm the KMS key ARN is region-local before automating this, and log copy duration — a 500GB+ snapshot can take 30-60 minutes, which directly eats into your RTO budget if you haven't accounted for it. See the AWS RDS snapshot copy docs for the full parameter list and region-specific caveats.

Watch Snapshot Storage Costs During Test Cycles

Automated RDS backups cap out at 35 days retention and expire on their own. Manual snapshots don't — they sit there billed per GB-month indefinitely until someone deletes them, and if you're running weekly restore tests without cleanup, that bill creeps up quietly for months before anyone notices. Storage cost tracks the actual used size within the snapshot, not the allocated instance storage, but frequent test cycles still add up fast on multi-hundred-GB databases.

Automate deletion of test-restore instances and snapshots with a Lambda that scans for the Purpose=restore-test tag and an expired ExpiryDate, then deletes both the instance and any snapshots it spawned. I stopped relying on manual cleanup after finding four forgotten restore-test instances running for two months — that's real money for zero value.

Lock Down IAM and Encryption for Restore Operations

Restore permissions are more powerful than most teams realize, and they're frequently over-provisioned at the account level instead of scoped per environment. Restrict rds:RestoreDBInstanceFromDBSnapshot and rds:CopyDBSnapshot to specific roles using IAM condition keys — anyone with broad RDS access can otherwise spin up a full copy of production data in a subnet you never intended.

Two gotchas worth flagging explicitly. First, KMS key policies must grant kms:CreateGrant to the restore role or cross-account/cross-region restores fail with KMSKeyNotAccessibleFault — a confusing error that has nothing to do with the RDS permissions themselves. Second, restoring always creates a brand-new instance (there's no in-place restore in RDS), and storage class or public accessibility settings can silently default differently than the source — always pass --no-publicly-accessible explicitly rather than trusting the default. Check the RDS restore documentation before scripting this into your pipeline, and pin your tooling — AWS CLI v2.15+ and Terraform's aws provider ~> 5.40 both have relevant fixes for snapshot copy support.

None of this is exotic. It's just the difference between a backup strategy that looks good on a dashboard and one that survives an actual incident. If your restore automation lives inside a broader Terraform or Ansible pipeline, it's worth reviewing how the rest of your infrastructure automation handles secrets and state before you wire restore-testing into CI.