Recovery Model in AWS SQL RDS

by

Recovery Model is the most important concept in Microsoft SQL Server. Recovery model controls type of backups taken for a database. It can also control transactional log truncation and Point-in-Time Recovery of a database.

On any full-fledged SQL server instance deployed on a physical server or a virtual machine, we can have various databases with different recovery models. But this is not the scenario when we deal with AWS SQL RDS.

Then what is the Scenario ?

On a AWS SQL RDS instance , we can have multiple databases but all would have same recovery model. Recovery model for databases can be controlled by AWS Portal Automatic Backups Settings.

Automated Backup…What is that?

Keeping this option enabled helps in point in time recovery of databases within backup retention period. If disabled, database recovery to point in time is not possible.

When we configure and create an AWS SQL RDS instance, we see above backup related settings. We can also see these backup settings on an already created AWS SQL RDS instance from AWS Portal.

Search for RDS –> Click on Databases –> Select any SQL RDS Instance –> Go to Maintenance & Backups Section to see below settings.

We can also modify these backup settings later. To disable automated backups retention should be set to 0. Any non-zero value keeps automated backups enabled.

Why we are focusing so much on this backup setting ?

Because this automated backups enabled or disabled at AWS Portal controls Recovery Models. So this is very important to understand the impact before making any change on this.

  1. Automated Backup enabled :–
    All databases for SQL RDS instance will have FULL Recovery Model. Even if one changes a recovery model to SIMPLE or BULK_LOGGED with backup retention set to non-zero value, RDS resets the recovery model to FULL within five minutes. This also results in RDS taking a snapshot of the DB instance.
  2. Automated Backup disabled :
    It means backup retention is set to 0 days. RDS sets the recovery mode to SIMPLE for all the databases. If you change a database’s recovery model from SIMPLE to any other option while backup retention is set to 0 days, RDS resets the recovery model to SIMPLE.

I hope you find it interesting to know if not already known.

Happy Learning !

Post Views: 81