India’s Leading Domain & Web Hosting Provider
Support
Domain & Login
Blog
Database Backup & Recovery: Point-in-Time, Corruption and Speed
Database backup and recovery is the practice of taking consistent, restorable copies of databases and their transaction logs so they can be returned to any chosen moment, whether after hardware loss, a bad deployment or an accidental bulk update. NTSPL designs PITR, log backup, validation and fast-restore methods for managed and self-managed databases on AWS, Azure and Google Cloud.
Delivered on
In scope
Three Ways Databases Need Recovering and Why Replicas Do Not Help
Databases fail differently from servers. The common incident is not a lost disk; it is a script that updated 4 lakh rows without a WHERE clause, a migration that dropped a column, or an integration that wrote wrong values for six hours before anyone noticed. Replicas and failover copy the damage instantly. Recovery then depends on whether you can return to the minute before the mistake, extract the good data and merge it back without losing the transactions that happened since. Most teams have nightly full backups and default seven-day retention, and have never timed a restore of their largest database.
NTSPL designs database protection around three recovery scenarios: whole-instance loss, logical corruption with a known start time, and slow-burn corruption discovered days later. For each we set the mechanism (managed PITR, native log backups, snapshots or exports), the retention window, the validation method and the restore technique, such as side-by-side restore and row-level reconciliation. Large databases get restore-speed engineering, so a 3 TB recovery fits inside the RTO rather than taking a day.
Signs Your Database Recovery Is Not Ready
Signals you need Database Backup & Recovery
- A bad update, deployment or integration has corrupted data in the last year and recovery took more than a day or lost legitimate transactions.
- Your largest database is above 1 TB and its full restore has never been timed against the agreed RTO.
- PITR retention is at the default of seven days or less, while errors are often discovered at month-end close.
- Self-managed SQL Server, Oracle or PostgreSQL on VMs is protected by VM snapshots only, without native log backups.
- Auditors or regulators have asked for proof of database restore and integrity checks, not just backup job success.
When to choose something else
- SAP HANA databases need Backint integration, log backup to cloud storage and HANA-specific consistency. See SAP Backup & Recovery in the SAP on Cloud family.
- You are moving a database to a different engine. Migration and engine change belong to the migration and modernisation families.
- Your requirement is regional failover of a healthy database, not recovery from data loss. Cross Region Recovery or Warm Standby DR covers that.
What NTSPL Designs for Each Database
Recovery scenario matrix
For each database, the expected recovery approach for instance loss, recent logical corruption and late-discovered corruption, with target times.
Backup and log mechanism
Managed PITR, native full, differential and log backups, storage snapshots or logical exports chosen per engine and deployment model.
Retention windows
PITR window, long-term full backups and archived exports sized to discovery lag, month-end and audit cycles, and legal retention.
Integrity validation
Scheduled restores with engine-native consistency checks such as DBCC CHECKDB, amcheck or RMAN validate, plus a business query per database.
Side-by-side restore and reconciliation
Procedures and scripts to restore to a new instance, compare affected tables with production and merge corrected rows safely.
Restore-speed engineering
Techniques such as striped backups, parallel restore, snapshot-based restore and pre-sized target instances for databases above 500 GB.
How Database Backup & Recovery Works, Step by Step
Database Recovery Paths: Instance Loss, Point-in-Time and Side-by-Side
Continuous log capture and periodic full backups feed three recovery paths. Instance loss restores to latest; recent corruption restores to a chosen time; late-discovered corruption restores side-by-side and reconciles only affected rows back into production.
Select any component
Database Recovery Paths: Instance Loss, Point-in-Time and Side-by-Side: click any of its 9 components to see its role and the design choice NTSPL makes for it, or trace the 7-step primary path.
Database Recovery Mechanisms on Each Cloud
Managed engines restore PITR to a new instance, which suits side-by-side recovery; Aurora MySQL Backtrack can rewind in place within its window for fast reversal of recent errors.
Azure SQL keeps PITR for 1-35 days and long-term retention up to 10 years; SQL Server on VMs uses Azure Backup's workload backup with log backups as frequent as every 15 minutes.
Cloud SQL and AlloyDB restore PITR into a new instance or cluster, which is the natural route for side-by-side recovery and row reconciliation.
Retention, Validation and Restore-Speed Decisions
PITR window length
Set the window to the longest realistic time between a data error and its discovery. For transactional systems that reconcile monthly, that is 35 days, the maximum on most managed engines. For systems reconciled daily, 14 days is usually enough. Beyond the PITR window, keep monthly full backups for 13 months and yearly ones as legal retention requires; accept that these restore only to their backup moment.
Snapshot versus native backup
Storage or VM snapshots restore large databases quickly but give crash- or app-consistent points only at snapshot time. Native log backups give minute-level PITR. For self-managed SQL Server, Oracle or PostgreSQL above 100 GB, use both: native logs every 5-15 minutes for precision, snapshots daily for fast bulk restore, and test that logs can be applied on top of a snapshot-based restore.
In-place rewind or side-by-side
Rewind in place only when the error is recent, isolated in time and nothing legitimate happened since, typically under 30 minutes. Otherwise restore side-by-side to a new instance and merge affected rows back. Side-by-side preserves legitimate transactions, keeps production online and lets business users verify corrected data before it goes live.
Restore speed for large databases
If a full restore exceeds 50% of RTO, engineer it. Options: stripe native backups across 8-16 files, restore onto storage with higher provisioned throughput then downsize, use snapshot-based restore paths, or keep a delayed replica that lags by a set number of hours as an extra recovery point. Measure restore throughput in MB per second and project it to full size.
Database Recovery Programme Steps
Database inventory and scenario matrix
Every in-scope database is listed with engine, size, write rate, owner and recovery target for each of the three scenarios.
Mechanism and retention configuration
PITR windows, native log backups, long-term retention and encryption are configured and documented per database.
Restore-speed tests
The largest databases are restored in full and timed, and striping, throughput or snapshot changes are applied where needed.
Side-by-side and reconciliation rehearsal
A simulated bad update is recovered side-by-side with business users validating merged data.
Automated integrity checks
Scheduled restores with consistency checks and a business query are automated, with results stored as evidence.
What you receive
Database Recoverability Measures
| KPI | Typical target |
|---|---|
| Databases with PITR window meeting discovery lag | 100% of Tier 1 and Tier 2 |
| Age of newest log backup | Within twice the configured interval |
| Largest database full restore time | Under 50% of RTO |
| Scheduled restores passing integrity checks | 100%, failures investigated within 24 hours |
| Time to recover from a simulated bad update | Under 4 hours without losing later transactions |
Database Recovery Pitfalls and How NTSPL Handles Them
| Risk | How NTSPL prevents it |
|---|---|
| Logs not backed up because of a silent agent or permission failure | Alerting on newest log backup age rather than job status, so missing logs are caught within one or two intervals. |
| In-place PITR overwrites legitimate transactions | Side-by-side restore is the default for anything beyond a short, isolated window, with business sign-off before data is merged. |
| Restored copies expose personal data in test environments | Restores outside production run through masking scripts before access, aligned with DPDP purpose limitation. |
| Backup encryption key unavailable at restore time | Keys for backups are held outside the database account or subscription, with restore tested using those keys. |
Database replicas vs database backups: aren't replicas enough?
No. Replicas, availability groups and multi-zone deployments protect against infrastructure failure by copying every change within seconds, including deletes, bad updates and ransomware encryption. Backups with transaction logs let you go back to a time before the damage. You need both: replicas for availability, backups and PITR for recoverability. A delayed replica that applies changes a few hours late sits in between and can be useful for very large databases, but it is not a substitute for backups with retention, because its window is short and it is still writable by the same administrators.
Key facts: Database Backup & Recovery
Database Backup & Recovery in Practice
2 Database Backup & Recovery scenarios, from Microfinance (NBFC-MFI loan servicing) and Warehousing (warehouse management system), plus related platform case studies.
Microfinance lender reverses a faulty EMI batch and keeps 9 hours of collections
3PL warehouse WMS cuts 2.4 TB database restore from 11 hours to 2 h 40 min
NBFC moves loan-origination Oracle database to Aurora PostgreSQL and drops EE licences
Microfinance lender moves loan SQL Server to Managed Instance in an 18-minute cutover
Database Backup & Recovery: Frequently Asked Questions
What does a database backup and recovery engagement cost?
For 10-30 databases across two or three engines, expect six to seven weeks and 30-45 consultant days. Run cost changes are mostly longer PITR retention and long-term backup storage, often INR 10,000-60,000 a month per large database. NTSPL prices the retention design before changes are applied.
How far back can managed cloud databases recover?
Amazon RDS and Aurora support PITR up to 35 days. Azure SQL Database and Managed Instance support 1-35 days with long-term retention up to 10 years. Cloud SQL and AlloyDB also support PITR windows that can be configured; beyond them, keep scheduled long-term backups.
Can we recover only a few tables?
Usually by restoring the whole database side-by-side, then copying or merging the needed tables or rows. Most managed engines restore at instance or database level. NTSPL keeps reconciliation scripts ready so table-level recovery takes hours, not days. Restoring the full database to a separate instance also keeps production online during the recovery.
Do RBI or SEBI rules affect database backup retention?
Yes. Regulated entities must retain records for periods set by their sector rules and the PMLA, often five to ten years, and RBI expects tested backups. NTSPL maps retention per data class and ensures long-term copies stay in India and are restorable.
How do we protect databases against ransomware?
Keep at least one backup copy in a locked or logically air-gapped vault that database administrators cannot delete, encrypted with keys outside the production account. Combine this with the Immutable & Protected Backup design so a compromised DBA account cannot erase recovery points.
Should backups be tested with DBCC CHECKDB or amcheck?
Yes. A restore that opens can still contain corruption. NTSPL runs engine-native consistency checks on a restored copy, not production, at least monthly for Tier 1 databases and quarterly for others, which also avoids loading production during business hours. Failures are escalated to the DBA lead the same day.