Dev and test Azure SQL databases with point-in-time restore retention above 7 days

Point-in-time restore storage on databases nobody restores

Azure SQL Database takes full, differential and log backups automatically so you can restore to any point inside the short-term retention window. The automated backups overview puts that window at 7 days by default, configurable between 1 and 35 days (Basic databases between 1 and 7). In the vCore model you are not charged for backup storage up to the database’s maximum data size; beyond that, backup storage is billed, and Microsoft notes that on General Purpose it costs more per GB than the data storage itself. In the DTU model, point-in-time restore storage is included in the database price, so a shorter window there saves nothing.

A development copy of a production database often inherits a 14- or 35-day window. With a busy test suite writing and rewriting data, log backups pile up and the database starts paying for restore points that nobody will ever use.

Checking PITR retention and backup size

Read the short-term retention policy for a database, and list the server’s databases with their size limit and backup redundancy:

az sql db str-policy show --resource-group my-rg --server my-server --name my-dev-db

az sql db list --resource-group my-rg --server my-server \
  --query "[].{name:name, sku:currentServiceObjectiveName, maxBytes:maxSizeBytes, redundancy:currentBackupStorageRedundancy}" \
  -o table

Backup volume is reported as separate daily metrics per backup type:

az monitor metrics list --resource <database-resource-id> \
  --metric full_backup_size_bytes diff_backup_size_bytes log_backup_size_bytes \
  --aggregation Maximum --interval PT24H --offset 30d

Gates for a dev or test database

1. The database name contains dev, test, qa, staging, sandbox or demo, and does not point to tooling such as devops, runner, agent, bastion or jumpbox.
2. Its short-term retention, read from the database’s backup retention policy, is longer than 7 days.
3. Its maximum data size is known, because that is the free backup allowance.
4. A measured backup storage figure exists for the database and is larger than the allowance.
5. A per-GB backup storage rate is known for its redundancy, locally or geo-redundant.

Databases this rule does not touch

Anything not named as dev or test is outside scope; a production recovery window is not a cost setting. When the backup total, the size limit or the rate is unavailable, or the backups still fit inside the free allowance, ZopNight raises no finding instead of estimating from the bill. Long-term retention (LTR) backups are a separate feature and are not assessed here.

Billable backup storage times the excess share

billable GB    = max(0, backup storage used - max data size)
excess share   = (retention days - 7) / retention days
monthly saving = billable GB x excess share x backup rate per GB-month
                 capped at the database's monthly cost

Only the billable part counts. A 35-day window trimmed to 7 removes four fifths of the excess, but a database whose backups sit inside the allowance saves nothing.

Setting a 7-day window on dev databases

1. Agree with the team that 7 days of point-in-time restore is enough for this database.
2. Update the policy: az sql db str-policy set --resource-group my-rg --server my-server --name my-dev-db --retention-days 7.
3. Consider locally redundant backup storage for non-production; Microsoft notes changes to backup redundancy apply only to future backups and can take up to 48 hours.