RDS Backups, Restores and RDS Custom

By the end of this lesson you will be able to

• Explain how RDS automated backups work (a daily snapshot plus transaction logs every 5 minutes) and set the retention period and backup window.
• Restore a database to a chosen second with point-in-time restore (PITR), know the latest restorable time, and explain why every restore creates a new instance with a new endpoint.
• Reconnect the application after a restore with a Route 53 CNAME, a config change or the rename trick.
• Use manual snapshots: keep, copy across Regions, share across accounts (including encrypted ones), and know what survives when you delete an instance.
• Design long-term retention for compliance with scheduled snapshots, AWS Backup and export to S3.
• Choose between stopping an instance and snapshot-and-delete to save money, and restore a MySQL database from S3.
• Run a major upgrade with Blue/Green Deployments and explain switchover, guardrails and rollback.
• Decide between RDS, RDS Custom and a database on EC2, and customise an RDS Custom instance safely.

#Why backups are a separate lesson

Lesson 7.1 showed how Multi-AZ and read replicas keep a database available. None of that protects you from the most common database disaster: a person or a bug destroying data. A DROP TABLE, an UPDATE without a WHERE, a migration script run against production: Multi-AZ copies the mistake to the standby within milliseconds, and the read replicas follow a moment later. Only a backup from before the mistake brings the data back.

RDS gives you two kinds of backups, and almost every exam question in this area is about the difference:

#Automated backups

#What RDS does every day

When automated backups are on, RDS does two things:

1. A daily snapshot of the whole DB instance during the backup window. It is a storage-volume snapshot (the same EBS snapshot technology from lesson 5.1), so it backs up the entire instance, not single databases. The first one is full; later ones are incremental and store only changed blocks.
2. Transaction logs, uploaded to S3 every 5 minutes. Every change the database makes is written to its transaction log (the binlog in MySQL, the write-ahead log in PostgreSQL), and RDS ships those logs continuously.

A snapshot alone can only take you back to 03:00. The logs fill the gap: to rebuild 14:37, RDS starts from the 03:00 snapshot and replays every logged change until 14:37:00, then stops. That is point-in-time recovery (PITR).

#Retention period and backup window

A few rules that catch people out:

• Changing retention between 0 and a non-zero value causes an outage, because RDS has to start or stop the whole backup machinery. Read replicas need automated backups on the source, so you cannot set 0 on an instance that has replicas.
• Performance. On a Single-AZ instance the snapshot can briefly suspend storage I/O (seconds, longer on big busy volumes), so put the window in your quietest hour. On a Multi-AZ instance the backup is taken from the standby, so the primary is not affected (lesson 7.1).
• Backups only run while the instance is available. While it is stopped, no automated backups are made, and the stopped time does not count toward retention.

#Point-in-time restore and the latest restorable time

• You can restore to any second from the earliest restorable time (the start of the retention window) to the latest restorable time.
• The latest restorable time is usually within the last 5 minutes, because that is how often logs reach S3. You see it in the console under Automated backups, or as LatestRestorableTime in describe-db-instances.
• If it is 15:00 and the last upload covered 14:55, a restore to 14:58 is refused. Choose 14:55.

#Restore always creates a new instance

This is the single most important exam fact in this lesson: a restore never overwrites the database you have. Whether you restore from an automated backup (PITR) or from a manual snapshot, RDS builds a brand-new DB instance with a new identifier and therefore a new endpoint, for example prod-db-1437.c9xyz0example.us-east-1.rds.amazonaws.com.

Why does AWS work this way? Safety. Imagine restoring the wrong backup over a running production database: everything written since that backup would be gone forever. With a new instance, the original keeps running, you get the restored copy beside it, you can compare the two, copy back only the rows you need, and delete the old one when you are sure.

What a restore gives you, and what it does not:

• The restored instance gets the default parameter group and option group (you can name custom ones on the CLI), and you choose the security group. Check them, or the app may fail to connect.
• You can choose a different instance class, but you cannot shrink storage.
• The new volume is lazy loaded from S3: it is usable immediately, but the first reads of each block are slower until initialisation finishes (the same behaviour as EBS volumes from snapshots in lesson 5.1).
• It takes time: minutes to hours, depending on size and how many logs must be replayed. Point-in-time restore is not an instant undo button. (Aurora has one, Backtrack, in lesson 7.3.)

#Pointing the application at the restored database

The restored instance has a new endpoint, and your web application does not know it. Three ways to fix that:

With the CNAME method, keep the record’s TTL short (for example 60 seconds), or clients will keep using the cached old endpoint for a while. Storing the endpoint in AWS Systems Manager Parameter Store or Secrets Manager and having the app read it at start-up is the same idea one level up.

#Manual snapshots

A manual snapshot (also called a DB snapshot) is a snapshot you ask for: in the console, with aws rds create-db-snapshot, from a script or Lambda function, or from AWS Backup.

• It is kept until you delete it, whatever the retention setting, and it survives deleting the instance.
• It restores to exactly the moment it was taken. There are no logs, so there is no point-in-time choice.
• Snapshots are incremental (only changed blocks are stored), but each one can be restored on its own.
• Quota: 100 manual snapshots per Region by default (can be raised). Automated backups do not count toward it.
• Use it before risky work (an upgrade, a big migration), before deleting an instance, and for anything you must keep longer than 35 days.

#Copying snapshots

copy-db-snapshot makes a new snapshot from an existing one:

• Within a Region: to turn an automated snapshot into a manual one (so it outlives the retention period and can be shared), or to change its encryption key.
• To another Region: for disaster recovery or to launch the database in another Region. KMS keys are Regional, so you name a key in the destination Region and the copy is re-encrypted with it. The first copy to a Region is a full copy; later copies of the same instance can be incremental.
• You can encrypt an unencrypted snapshot while copying it. That is the standard way to encrypt an existing database: snapshot, encrypted copy, restore (lesson 7.4).

#Sharing snapshots with other accounts

You can share a manual snapshot with up to 20 AWS accounts, or make it public (only if unencrypted, and never with real data).

Lesson 7.4 explains the key policies. Two more limits: a snapshot whose option group has permanent or persistent options (for example Oracle or SQL Server TDE) cannot be shared, and snapshots of Multi-AZ DB clusters cannot be shared.

#Deleting an instance: what survives

When you delete a DB instance, the delete screen (or CLI flags) asks three questions, and they decide what you can restore later:

1. Deletion protection

   If deletion protection is on, the delete call fails until someone modifies the instance to turn it off. Turn it on for every production database: it turns an accidental delete-db-instance into a deliberate two-step action.

2. Create a final snapshot?

   RDS can take one last manual snapshot before deleting (--final-db-snapshot-identifier). Like every manual snapshot, it is kept until you delete it. With the CLI you must choose explicitly: a final snapshot identifier or --skip-final-snapshot.

3. Retain automated backups?

   By default the automated backups are deleted with the instance (on the CLI, --delete-automated-backups is the default). If you choose Retain automated backups (--no-delete-automated-backups), they stay as a retained backup: you can still restore to any second in the remaining window, but they expire when the retention period runs out, and you pay for their storage until then.

#Long-term retention for compliance

Banks, hospitals and listed companies often must keep database backups for 1 to 7 years for audits or law. Automated backups cannot do that: their maximum retention is 35 days, and RDS deletes older ones automatically. Your options:

#Saving money: stop, or snapshot and delete?

A database that is needed only now and then still costs money every hour it runs.

So: stop for a night, a weekend or a few days. For weeks or months of idle time, snapshot and delete, then restore when needed. (A scheduled job can also re-stop an instance every 7 days, but you still pay for storage.) The slides call this the “trick”: a stopped database still pays for storage.

#More ways to restore

The slides list “restore MySQL RDS from S3” without naming the tool and mention Percona XtraBackup only for Aurora. In fact RDS for MySQL also requires Percona XtraBackup backups for this; a plain mysqldump file is imported by running it against an instance, not with restore from S3. Aurora’s version of this restore belongs to lesson 7.3.

#Blue/Green Deployments

Major version upgrades, big schema changes and parameter changes are scary on a production database: an in-place major upgrade takes the database down for a while, and if the new version misbehaves there is no quick way back. Amazon RDS Blue/Green Deployments solve this with a synchronised copy:

1. Create

   RDS copies the whole production topology (Multi-AZ, read replicas, storage settings, monitoring) into a green environment. You can upgrade green’s engine version and give it a new parameter group at creation. Blue is production; green is read-only by default.

2. Keep in sync

   RDS replicates every change from blue to green. RDS for MySQL and MariaDB use binlog replication, which is logical replication: a form of change data capture (CDC). RDS for PostgreSQL uses physical replication, or logical replication when green is on a higher major version.

3. Test

   Point a test copy of the application at green’s own endpoint: run queries, check performance, try schema changes (keep green read-only if you can, because writes there can break replication).

4. Switch over

   RDS runs guardrails, stops writes on both sides, drops connections, waits until green has every change, then swaps names and endpoints: green becomes prod-db, blue becomes prod-db-old1. The application needs no change. It usually takes under a minute, with no data loss.

Details worth knowing:

• Guardrails block the switchover if replication is unhealthy, the replica lag is too high for the timeout, or there are long-running writes or DDL statements on blue (and, for PostgreSQL logical replication, unsupported changes such as DDL on blue). Nothing is changed when a guardrail blocks.
• Switchover timeout: 30 to 3,600 seconds, default 300. If the switchover takes longer, everything is rolled back and both environments are left as they were.
• Rollback before switchover is simple: delete the Blue/Green Deployment (and the green instances). Production was never touched.
• After switchover there is no automatic switch back: replication between the two stops, and the old blue instances are kept (read-only, still billed) until you delete them.
• Keep the client DNS cache short (RDS uses a 5-second TTL); RDS Proxy and AWS’s smart drivers notice the switch faster than DNS.
• Engines: RDS for MySQL, MariaDB and PostgreSQL, plus Aurora MySQL and Aurora PostgreSQL (lesson 7.3). Not RDS for Oracle, SQL Server or Db2.

#RDS Custom

#The problem: RDS keeps you out of the server

Standard RDS runs your database on an EC2 instance in an AWS-owned account. You get a database endpoint and nothing else: no SSH, no operating system access, no root, and some high-privilege database procedures are blocked. That is what lets AWS patch, back up and repair everything for you.

Some applications cannot live with that. Packaged business software (accounting suites, banking cores) often needs a vendor agent installed on the database server, a specific OS or database patch, special file system settings or a database feature that needs privileges RDS does not give. Before RDS Custom the only choice was to run the database yourself on EC2 or on-premises and do all the administration.

#What RDS Custom is

Amazon RDS Custom is a managed database service for Oracle and Microsoft SQL Server only that gives you administrative access to the underlying operating system and database:

• The EC2 instance and its EBS volumes live in your account. You connect with SSM Session Manager or SSH (Oracle), or RDP (SQL Server).
• RDS Custom still automates setup, backups and PITR, monitoring and instance recovery (if the host is impaired, it reboots or replaces it).
• You can install software and agents, apply custom patches, configure settings and enable native features.
• Licensing: Oracle is bring your own licence (BYOL), with your own installation media. SQL Server can be license included or bring your own media.

#Pausing automation

RDS Custom’s automation watches the host and “repairs” anything unexpected. If you change files or packages while it is watching, it may treat your change as a fault. So the procedure is:

1. Take a DB snapshot first, so you can go back.
2. Pause automation (--automation-mode all-paused) for 60 minutes by default, up to 1,440 minutes (24 hours). You can extend a running pause.
3. Connect (Session Manager or SSH/RDP) and customise.
4. Resume full automation (--automation-mode full), or let the pause expire and resume by itself.

The slides call this “de-activating automation mode”; AWS calls it pausing automation.

If a change moves the instance outside what RDS Custom can manage (its support perimeter), the status becomes unsupported-configuration until you fix it yourself.

#Shared responsibility shifts toward you

The more you can change, the more you can break: with RDS Custom, making your customisations work is your job.

#RDS, RDS Custom or EC2?

#How backups fit into system design

• Backups are your last line of defence. Multi-AZ protects against hardware and AZ failure, read replicas scale reads, but only backups undo human error and corruption. Use all three.
• Set RPO and RTO first. PITR gives an RPO of about 5 minutes; the RTO is the restore time (minutes to hours, growing with database size) plus the time to switch the app. Practise restores regularly and time them: an untested backup is a hope, not a plan.
• Put a name you own in front of the database (a Route 53 CNAME or a secret) so a restore or migration is a one-record change.
• Production checklist: retention 7–35 days, backup window in the quietest hour, deletion protection on, final snapshot on delete, encryption with a customer managed key if snapshots will ever be shared, cross-Region copies or automated backup replication for DR, AWS Backup with Vault Lock for compliance.
• Changes through Blue/Green: major upgrades and risky parameter changes go through a Blue/Green Deployment instead of in place.
• Watch the bill: backup storage beyond your database size, retained backups of deleted instances, forgotten manual snapshots, and old blue instances after a switchover all cost money.

#Try it yourself

DB=prod-db

# 1. Retention 14 days and a backup window at 03:00 UTC (applied now)
aws rds modify-db-instance --db-instance-identifier $DB \
  --backup-retention-period 14 --preferred-backup-window 03:00-03:30 \
  --deletion-protection --apply-immediately

# 2. See the PITR window
aws rds describe-db-instances --db-instance-identifier $DB \
  --query "DBInstances[0].[BackupRetentionPeriod,LatestRestorableTime]"
aws rds describe-db-instance-automated-backups --db-instance-identifier $DB \
  --query "DBInstanceAutomatedBackups[0].RestoreWindow"

# 3. Point-in-time restore to 14:37 into a NEW instance
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier $DB \
  --target-db-instance-identifier prod-db-1437 \
  --restore-time 2026-10-25T14:37:00Z \
  --db-parameter-group-name prod-params --vpc-security-group-ids sg-0abc1234def567890
aws rds wait db-instance-available --db-instance-identifier prod-db-1437

# 4. Swing the CNAME the app uses to the new endpoint
NEW=$(aws rds describe-db-instances --db-instance-identifier prod-db-1437 \
  --query "DBInstances[0].Endpoint.Address" --output text)
aws route53 change-resource-record-sets --hosted-zone-id Z0123456789EXAMPLE \
  --change-batch "{\"Changes\":[{\"Action\":\"UPSERT\",\"ResourceRecordSet\":{\"Name\":\"db.shop.example\",\"Type\":\"CNAME\",\"TTL\":60,\"ResourceRecords\":[{\"Value\":\"$NEW\"}]}}]}"

#    ...or the rename trick
aws rds modify-db-instance --db-instance-identifier prod-db --new-db-instance-identifier prod-db-old --apply-immediately
aws rds modify-db-instance --db-instance-identifier prod-db-1437 --new-db-instance-identifier prod-db --apply-immediately

# 5. Manual snapshot, copy to another Region, share with another account
aws rds create-db-snapshot --db-instance-identifier $DB --db-snapshot-identifier snap-0601
aws rds copy-db-snapshot --region eu-west-1 \
  --source-db-snapshot-identifier arn:aws:rds:us-east-1:111122223333:snapshot:snap-0601 \
  --target-db-snapshot-identifier snap-0601-dr --kms-key-id alias/rds-dr
aws rds modify-db-snapshot-attribute --db-snapshot-identifier snap-0601 \
  --attribute-name restore --values-to-add 444455556666

# 6. Cross-Region automated backup replication (PITR in eu-west-1)
aws rds start-db-instance-automated-backups-replication --region eu-west-1 \
  --source-db-instance-arn arn:aws:rds:us-east-1:111122223333:db:prod-db \
  --backup-retention-period 7 --kms-key-id alias/rds-dr

# 7. Export a snapshot to S3 as Parquet for Athena
aws rds start-export-task --export-task-identifier snap-0601-export \
  --source-arn arn:aws:rds:us-east-1:111122223333:snapshot:snap-0601 \
  --s3-bucket-name shop-db-exports --iam-role-arn arn:aws:iam::111122223333:role/rds-s3-export \
  --kms-key-id alias/exports

# 8. Delete safely: final snapshot, keep automated backups
aws rds modify-db-instance --db-instance-identifier old-db --no-deletion-protection --apply-immediately
aws rds delete-db-instance --db-instance-identifier old-db \
  --final-db-snapshot-identifier old-db-final --no-delete-automated-backups

# 9. Stop a dev database (it starts again by itself after 7 days)
aws rds stop-db-instance --db-instance-identifier dev-db --db-snapshot-identifier dev-db-before-stop

# 10. Restore an on-premises MySQL backup made with Percona XtraBackup
xtrabackup --backup --user=admin --password=... --stream=xbstream \
  --target-dir=/backup | split -d --bytes=500MB - /backup/full.xbstream
aws s3 cp /backup/ s3://shop-mysql-import/full/ --recursive
aws rds restore-db-instance-from-s3 --db-instance-identifier shop-mysql \
  --db-instance-class db.m6g.large --engine mysql --allocated-storage 200 \
  --master-username admin --manage-master-user-password \
  --source-engine mysql --source-engine-version 8.0.36 \
  --s3-bucket-name shop-mysql-import --s3-prefix full \
  --s3-ingestion-role-arn arn:aws:iam::111122223333:role/rds-s3-import

# 11. Blue/Green: upgrade MySQL 8.0 -> 8.4, then switch over with a 10-minute timeout
aws rds create-blue-green-deployment --blue-green-deployment-name prod-84 \
  --source arn:aws:rds:us-east-1:111122223333:db:prod-db \
  --target-engine-version 8.4.6 --target-db-parameter-group-name prod-params-84
aws rds describe-blue-green-deployments --query "BlueGreenDeployments[].[BlueGreenDeploymentIdentifier,Status]"
aws rds switchover-blue-green-deployment \
  --blue-green-deployment-identifier bgd-1234567890abcdef --switchover-timeout 600

# 12. RDS Custom: pause automation for 2 hours, connect, resume
aws rds modify-db-instance --db-instance-identifier erp-oracle \
  --automation-mode all-paused --resume-full-automation-mode-minutes 120
aws ssm start-session --target i-0abc1234def567890
aws rds modify-db-instance --db-instance-identifier erp-oracle --automation-mode full

And the accident this lesson is about, in SQL. Note the time before you do anything else:

SELECT NOW();               -- 2026-10-25 14:38:12: the mistake happened just now
DROP TABLE orders;          -- the mistake
-- Restore to 14:37:59 (step 3 above), then copy only the lost table back if production
-- has received new, good writes since:
--   mysqldump -h prod-db-1437... shop orders | mysql -h prod-db... shop

#Summary

Key takeaways

• Automated backups = a daily snapshot in the backup window + transaction logs every 5 minutes → PITR to any second in the window, up to the latest restorable time (about 5 minutes ago).
• Retention 0–35 days; 0 disables automated backups. Default 7 days (console), 1 day (CLI/API). Multi-AZ instances back up from the standby.
• Every restore creates a new instance with a new endpoint. Reconnect the app by editing its config, updating a Route 53 CNAME (no code change) or renaming the instances.
• Manual snapshots last until you delete them, survive instance deletion and restore to their exact moment. Copy across Regions (re-encrypted with a destination key) and share with up to 20 accounts; automated snapshots must be copied first; snapshots encrypted with aws/rds cannot be shared, use a customer managed key.
• Deleting an instance deletes its automated backups unless you retain them (kept only until retention ends). For indefinite retention take a final snapshot. Turn on deletion protection.
• Compliance beyond 35 days → AWS Backup plans (with Vault Lock) or scheduled manual snapshots. Export to S3 (Parquet) is for analytics, not restore.
• Stopped instances still pay for storage and restart automatically after 7 days; for long idle periods, snapshot and delete.
• Other restores: cross-Region automated backup replication (PITR in another Region) and MySQL from S3 with Percona XtraBackup.
• Blue/Green Deployments (RDS MySQL, MariaDB, PostgreSQL): a synchronised green copy for upgrades and changes, guardrails, switchover usually under a minute, timeout default 300 s with automatic rollback; delete green to back out.
• RDS Custom (Oracle and SQL Server only): OS and database access via Session Manager, SSH or RDP; snapshot, pause automation (60 min default, 24 h max), customise, resume; more responsibility is yours. Other engines needing OS access → EC2.

Self-check: RDS backups, restores and RDS Custom30 questions