Cloud Backup, Point-in-Time Restore, and Geo-Replication

Cloud Backup, Point-in-Time Restore, and Geo-Replication

A database administrator's most important job is making sure data can be recovered when something goes wrong. Cloud platforms provide powerful tools for backup, restore, and replication, but those tools only protect you if they are configured correctly and tested regularly. This lesson walks through the core concepts and hands-on skills you need to protect databases on AWS and Azure.

Automated Backups and Retention

AWS RDS automatically takes daily snapshots of your database instance and captures transaction logs continuously. Together, these allow point-in-time restore (PITR), meaning you can recover the database to any second within your retention window. The retention period is set in whole days; AWS supports a range from 1 to 35 days for automated backups. Setting retention to 0 disables automated backups entirely, which is dangerous for any production workload and should be avoided.

When you enable automated backups, RDS stores both the base snapshot and the transaction logs in Amazon S3 in the same region. You must also consider whether those backups are themselves replicated across availability zones or regions — regional storage alone does not protect against a full regional outage.

Azure SQL Database uses a similar model. The platform automatically takes full, differential, and transaction-log backups and retains them for 7 days by default, configurable up to 35 days on most tiers. Azure stores backups in geo-redundant storage (GRS) by default, meaning copies are automatically replicated to a paired region.

Point-in-Time Restore

PITR lets you spin up a new database instance restored to a specific timestamp. On AWS RDS, you initiate a PITR from the console or CLI by specifying the source DB instance and the target restore time. The restored instance is a separate resource — your original instance keeps running. This is critical: PITR does not overwrite the current database. After restore, you must verify the recovered data, then redirect application connection strings to the new instance if you intend to use it as the new primary.

On Azure SQL Database, PITR is available through the portal, CLI, or REST API. You select the database, choose "Restore," and enter the target point in time. The result is a new database on the same or a different logical server.

Always record the exact timestamp of the event you are recovering from — for example, the time a table was accidentally dropped. Choose a restore time a few minutes before that event. After restore, run a query to confirm the recovered object exists before treating the operation as complete.

Recovery Objectives: RTO and RPO

Recovery Point Objective (RPO) is the maximum amount of data loss a business can accept, measured in time. If RPO is 1 hour, the business can tolerate losing up to 1 hour of transactions. Your backup frequency and log shipping interval must be shorter than the RPO.

Recovery Time Objective (RTO) is the maximum time allowed to restore service after a failure. If RTO is 4 hours, the database must be back online within 4 hours of an incident. PITR restore time depends on database size and may take longer than expected — always test restores in a non-production environment and time them with a lab timer or equivalent tool so you have real data for RTO planning.

When selecting backup and replication configurations, start with the business's stated RPO and RTO, then work backward to the required settings.

Multi-AZ Deployment on AWS RDS

Multi-AZ is a high-availability feature, not a backup feature. When enabled, RDS synchronously replicates data to a standby instance in a different Availability Zone within the same region. If the primary fails, RDS automatically fails over to the standby — typically within one to two minutes. This reduces downtime but does not protect against logical errors such as accidental table deletion, because the deletion is replicated to the standby immediately. For logical error recovery, you still need PITR.

Active Geo-Replication on Azure SQL Database

Active geo-replication allows you to create up to four readable secondary replicas in different Azure regions. Replication is asynchronous, so there is a small lag between the primary and secondary. This lag represents your practical RPO for a geo-replication scenario.

To initiate a manual failover from the portal, navigate to the geo-replication blade of your primary database and select the secondary you want to promote. Azure promotes the secondary to primary and demotes the original primary to a secondary role. After failover, applications must reconnect using the updated connection string for the new primary. On Azure SQL, if you use a failover group, the DNS endpoint automatically redirects connections, simplifying application reconfiguration.

Safety and Operational Discipline

While cloud DBA work does not involve physical electrical hazards requiring lockout/tagout, it carries its own critical safety practices. Always work in a non-production environment when testing restores or failovers; never execute failover commands against a live production instance without a formal change-control approval. Protect credentials: never embed AWS access keys or Azure connection strings in screenshots, scripts committed to shared repositories, or lab submission documents. Use IAM roles and Azure Managed Identity instead of hard-coded credentials. Verify that backup encryption is enabled — both AWS RDS and Azure SQL Database support encryption at rest, and this should be confirmed before a database is moved to production.