Backup, Recovery and Incident Response

Introduction

Database systems contain operational records, personal information, financial transactions, intellectual property and evidence needed for organisational accountability. Their loss or unauthorised alteration can interrupt services, violate legal obligations and undermine trust. Security therefore requires more than preventing attacks. It also requires the ability to restore reliable information within an acceptable time after accidental deletion, hardware failure, corruption, ransomware or deliberate compromise.

Backup, recovery and incident response are related but distinct disciplines. Backup creates recoverable copies of data. Recovery returns systems and information to an acceptable operational state. Incident response manages the broader process of detecting, containing, investigating, eradicating and learning from a security event. A mature programme integrates all three while preserving evidence and maintaining clear authority for decisions.

This chapter presents practical methods for designing secure database backup and disaster-recovery processes, applying recovery point objectives (RPO) and recovery time objectives (RTO), protecting backups from ransomware, and responding to database incidents while preserving chain of custody.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish backup, restoration, business continuity, disaster recovery and incident response.
2. Define and apply RPO, RTO, recovery consistency and service-level requirements.
3. Design backup schedules and storage architectures for relational and non-relational databases.
4. Protect backups against ransomware, insider abuse, deletion and silent corruption.
5. Plan and execute database restoration and disaster-recovery tests.
6. Apply the incident-response lifecycle to a database compromise.
7. Collect, document and protect digital evidence using chain-of-custody principles.
8. Evaluate governance, privacy, legal and professional responsibilities during recovery.

1. Foundations of Resilience

A backup is a separate, recoverable copy of data or configuration. A snapshot is a point-in-time representation, often dependent on the original storage system. A replica is a continuously or periodically updated copy used mainly for availability or read scaling. Replication is not automatically backup: corruption, malicious updates and accidental deletion may be replicated immediately.

Restoration is the technical act of retrieving data from backup or another recovery source. Disaster recovery (DR) is the coordinated restoration of technology services after a disruptive event. Business continuity is broader; it ensures that critical business processes continue, possibly through manual procedures or alternative facilities. Incident response addresses security events and includes investigation, containment and eradication as well as recovery.

Resilience depends on several properties:

• Availability: authorised users can access services when required.
• Integrity: data remains accurate, complete and unaltered except through authorised processes.
• Confidentiality: backup and production data are protected from unauthorised disclosure.
• Recoverability: data and services can be restored to a defined acceptable state.
• Recoverability evidence: the organisation can demonstrate what was restored, from which source and with what validation.

A sound design begins with business impact analysis. For each database, the organisation identifies critical processes, dependencies, maximum tolerable downtime, legal requirements, data sensitivity and acceptable data loss. Technical architecture should follow these business requirements rather than the reverse.

2. RPO, RTO and Recovery Design

Recovery Point Objective

The recovery point objective (RPO) is the maximum acceptable amount of data loss measured in time. If a system has an RPO of 15 minutes, recovery should normally return data to a point no more than 15 minutes before the disruption, subject to stated assumptions.

A shorter RPO usually requires more frequent log shipping, journaling, replication or continuous protection. RPO is not simply the backup interval: delays in log transfer, verification and storage availability must be considered.

Recovery Time Objective

The recovery time objective (RTO) is the maximum acceptable elapsed time from disruption to restoration of the required service. It includes detection, decision-making, access to recovery resources, restoration, validation and controlled release to users.

A database may have an excellent RPO but an unacceptable RTO if its backups are slow to retrieve or require extensive manual reconstruction. Conversely, a fast replica may offer a low RTO but poor protection against corruption.

Quantitative interpretation

If the last usable recovery point was at time t_b and the failure occurred at t_f, the realised data-loss interval is:

where both times use the same time zone and clock reference. The design meets the RPO when this interval is less than or equal to the approved RPO, assuming the recovery point is consistent and usable.

If service becomes available at t_r, the realised recovery duration is:

The design meets the RTO when this duration is less than or equal to the approved RTO. These relationships measure performance; they do not guarantee that recovery is successful. Integrity validation, application testing and user acceptance are additional requirements.

Worked example

A payroll database fails at 14:00. The latest verified transaction-log backup covers activity through 13:45, and the service is declared operational at 16:20. The data-loss interval is 15 minutes, so an RPO of 30 minutes is met. The recovery duration is 2 hours 20 minutes. Therefore, an RTO of 2 hours is not met, although an RTO of 3 hours is met. The organisation should investigate whether retrieval, compute provisioning, database recovery, validation or approval caused the delay.

Recovery tiers

Not every database requires identical protection. A useful tier model is:

The tier must be based on business impact, not merely database size or technology preference.

3. Backup Methods and Schedules

A full backup copies the selected database contents. A differential backup copies changes since the most recent full backup. An incremental backup copies changes since the most recent applicable backup, which may be full or incremental. Transaction-log, write-ahead-log or journal backups record changes needed for point-in-time recovery.

A schedule should specify frequency, retention, encryption, destination, ownership and testing. For example, a system might use weekly full backups, daily differential backups and transaction-log backups every 10 minutes, with longer retention for regulatory records. The precise schedule must be derived from RPO, storage capacity, workload and recovery testing.

Backups must include more than table data. Recovery often requires database schemas, stored procedures, users and permissions, encryption-key references, configuration files, connection settings, application binaries, dependencies, certificates and documented procedures. Secrets should not be copied indiscriminately into backup files; they should be managed through a secure secrets system and restored through controlled processes.

A backup is usable only when it is both available and consistent. Database-native backups generally understand transactions and checkpoints. File-system copies made while a database is running may contain mutually inconsistent files unless the database supports a suitable quiescing or snapshot mechanism.

4. The 3-2-1 Principle and Ransomware Resistance

A widely used baseline is the 3-2-1 principle: maintain at least three copies, on at least two different types of media or storage environments, with at least one copy geographically separate. Modern practice often extends this to 3-2-1-1-0: one copy offline or logically isolated, and zero unresolved errors after verification.

Important controls include:

• Immutability: retention locks or write-once controls prevent alteration before expiry.
• Air gap or logical isolation: backup credentials and network paths are unavailable from ordinary production accounts.
• Separate identities: backup administration uses dedicated accounts, multifactor authentication and least privilege.
• Separate management plane: attackers who compromise production should not automatically control backup deletion.
• Encryption: encrypt data in transit and at rest; protect keys separately with controlled rotation and recovery procedures.
• Tamper-evident logging: record backup creation, access, deletion attempts and policy changes in a protected log system.
• Malware screening: inspect restoration candidates and monitor unusual encryption or deletion patterns.
• Retention locks: prevent premature deletion, while providing documented legal-hold exceptions.
• Regular restoration tests: verify that a backup can produce a functioning, trustworthy database.

Ransomware resilience requires attention to the recovery environment itself. Restoring an infected database into an infected network can cause immediate reinfection. A clean-room or isolated recovery network should use trusted images, restricted connectivity, verified administrator accounts and carefully screened data. Backups should be scanned, but scanning alone is not proof that they are safe; historical versions, anomaly analysis and application-level validation are also important.

5. Restoration and Disaster-Recovery Procedures

A restoration runbook should be sufficiently detailed for a trained operator who did not design the original system. It should include contacts, authority thresholds, dependencies, credentials access procedures, backup locations, recovery order, validation checks, rollback steps and communication templates.

A typical restoration sequence is:

1. Declare the event and identify the responsible incident or recovery authority.
2. Preserve evidence and record the initial timeline.
3. Determine the failure scope and select a recovery point.
4. Establish a clean, isolated recovery environment.
5. Restore the database engine, configuration and baseline full backup.
6. Apply differential and transaction-log or journal backups in the correct sequence.
7. Verify database consistency, transaction state, users, permissions and encryption.
8. Reconnect dependent applications in a controlled order.
9. Conduct functional, integrity and security tests.
10. Obtain business-owner approval before returning to service.
11. Monitor closely and document deviations, decisions and lessons learned.

Point-in-time recovery requires careful selection of a target time. The target should be before the suspected compromise or corruption, while preserving legitimate transactions where possible. Investigators should retain the original evidence and perform recovery work on copies or controlled environments.

Validation should include database-native consistency checks, row counts or reconciliation against trusted sources, referential-integrity checks, application transactions, access-control tests and monitoring for unexpected processes or network connections. “The database starts” is not sufficient evidence of successful recovery.

Recovery testing

Testing types include:

• Tabletop exercise: participants discuss decisions and responsibilities.
• Component test: a particular backup or restoration step is tested.
• Technical recovery test: systems are restored in an isolated environment.
• Failover exercise: service is deliberately moved to an alternate environment.
• Full exercise: technical, business, communications and management procedures operate together.

Tests should measure actual RPO and RTO, identify undocumented dependencies and record corrective actions. A failed test is valuable evidence; concealing it creates operational risk.

6. Database Incident Response

An incident is an event that threatens confidentiality, integrity or availability, or violates security policy. Examples include stolen database credentials, unauthorised privilege escalation, mass extraction, suspicious changes, destructive SQL, ransomware, exposed backups and compromised replication systems.

A practical lifecycle contains the following phases:

Preparation

Preparation includes policies, roles, contact lists, logging, time synchronisation, monitoring, tested backups, forensic tools, legal contacts and exercises. Database audit logs should capture authentication, privilege changes, schema changes, sensitive queries and administrative actions where lawful and technically feasible. Logging must be protected from alteration and designed with privacy minimisation.

Detection and analysis

Indicators may include impossible-travel logins, new privileged accounts, unusual query volume, access outside working patterns, large exports, disabled auditing, unexpected schema changes or abnormal encryption activity. Analysts should establish what happened, when it began, which accounts and systems were involved, and whether data was viewed, changed, deleted or exfiltrated.

Alerting alone is not proof. Analysts correlate database logs with identity, operating-system, network, endpoint and cloud-control-plane records. Clock synchronisation and documented time zones are essential for a reliable timeline.

Containment

Short-term containment limits damage while preserving evidence. Actions may include disabling a compromised account, revoking tokens, isolating a host, blocking malicious network paths, stopping an exposed service or moving users to a controlled standby. Long-term containment may use a rebuilt environment or temporary access restrictions.

Containment decisions should be authorised and recorded. Immediately shutting down a system may destroy volatile evidence or interrupt critical services; leaving it online may allow continuing theft. The appropriate action depends on risk, legal advice and business impact.

Investigation and evidence preservation

Investigators should identify the source, affected assets, attack path, persistence mechanisms, compromised credentials and data impact. They should collect relevant logs, disk images, memory where justified, configuration, network records and copies of suspicious files. Collection should be proportionate, documented and authorised.

Eradication

Eradication removes malicious accounts, software, persistence mechanisms and unauthorised changes. Credentials and keys are rotated from trusted systems. Vulnerabilities are patched, insecure configurations are corrected and affected hosts may be rebuilt rather than merely cleaned. Recovery should not begin until the organisation has reasonable confidence that the attacker’s access has been removed.

Recovery

Systems are restored from verified clean sources, monitored intensively and returned to service in stages. Access is revalidated, database integrity is checked and business owners confirm that operations are correct. Additional safeguards may include enhanced logging, temporary network restrictions and heightened review of privileged actions.

Post-incident activity

The organisation conducts a lessons-learned review, completes required notifications, updates risk assessments and tracks corrective actions. The purpose is improvement, not blame. Reports should distinguish confirmed facts, reasonable inferences and unresolved questions.

7. Chain of Custody and Forensic Integrity

Chain of custody is the documented history of evidence from collection through storage, transfer, examination and presentation. It supports credibility by showing that evidence was handled consistently and was not silently altered.

A chain-of-custody record normally includes:

• unique evidence identifier;
• description, source and location;
• date and time of collection, including time zone;
• collector and authorising person;
• acquisition method and tools;
• cryptographic hash where appropriate;
• storage location and access restrictions;
• every transfer, recipient, purpose and signature or electronic approval;
• examination activity and resulting derivative copies.

A cryptographic hash is a fingerprint used to detect change. Hashing does not prove that evidence is truthful, nor does it establish who created the original. It supports integrity verification when combined with controlled acquisition, documentation and secure storage. Original evidence should be preserved as read-only where feasible; analysis should occur on verified working copies.

Evidence handling must respect privacy, employment law, data-protection requirements, privilege and jurisdiction. Investigators should avoid unnecessary collection of unrelated personal information and should consult legal or regulatory specialists when notification, law-enforcement disclosure or cross-border transfer is possible.

8. Applied Case: Ransomware in a Retailer’s Order Database

The following is an illustrative case. A medium-sized online retailer discovers that order records are encrypted and that a privileged database account created three days earlier exported customer information. The production database is unavailable, while a reporting replica is also encrypted.

Problem. The retailer must restore ordering within four hours, lose no more than 15 minutes of transactions, determine whether personal data was exfiltrated and preserve evidence for possible regulatory notification.

Analysis. The response team disables the suspected account and isolates the database network, but does not immediately delete affected systems. Identity, database, endpoint and firewall logs are copied to protected evidence storage. The replica is rejected as a recovery source because it contains the replicated encryption. The team identifies an immutable log-backup repository whose latest verified point is 12 minutes before the attack.

Approach. A clean recovery environment is created using trusted system images. The team restores the last clean full backup, applies differential and log backups to the selected point, verifies database consistency and tests an order in a staging application. Credentials, encryption keys and service identities are rotated through trusted administration systems. The business owner approves staged production release. Forensic analysts examine the compromised host and logs separately, preserving originals and documenting hashes and access.

Outcome. The 12-minute data-loss interval meets the 15-minute RPO. Service returns in three hours and 35 minutes, meeting the four-hour RTO. Investigation confirms unauthorised access to customer names and addresses but finds no evidence that payment-card data was stored in the affected database. The organisation completes its legal assessment, notifies relevant parties where required, removes the persistence mechanism and implements isolated backup administration.

Learning points. Replication did not replace immutable backup. Recovery objectives had to include validation, not merely server startup. Evidence preservation influenced containment decisions. Separate identity controls and tested clean-room procedures reduced recovery time. The incident also exposed the need to monitor privileged-account creation and unusual exports.

9. Governance, Ethics and Contemporary Practice

Senior management should approve recovery objectives, fund appropriate controls and accept residual risk explicitly. Database administrators, security teams, continuity managers, legal advisers and business owners require clearly defined responsibilities. Suppliers and cloud providers should be assessed for backup ownership, retention, geographic location, deletion controls, encryption and restoration support.

Privacy principles affect backups because retained copies may contain personal data long after production deletion. Retention must balance recovery, regulatory preservation and data minimisation. Access should be limited, restoration environments should be protected, and disposal should be demonstrably secure when retention expires.

Automation and artificial intelligence can help detect anomalous query behaviour, forecast backup capacity, prioritise alerts and identify unusual restoration patterns. However, automated decisions can produce false positives, expose sensitive data to an unapproved service or conceal model limitations. Human approval remains important for account disablement, evidence interpretation, legal notification and destructive recovery actions. AI-generated incident summaries must be checked against primary logs.

Common limitations include incomplete logging, clock drift, unknown dependencies, corrupted backup chains, insufficient bandwidth, untested procedures, cloud-provider outages and overconfidence in replication. No single control eliminates these risks. Defence in depth combines prevention, detection, resilient storage, tested recovery and disciplined investigation.

Best-Practice Checklist

• Define an owner, RPO and RTO for every important database.
• Use database-consistent backup methods and document dependencies.
• Maintain isolated, encrypted and immutable backup copies.
• Separate production, backup and recovery administration.
• Monitor backup success, retention changes and unusual access.
• Test restoration and measure actual recovery performance.
• Maintain current runbooks, contact lists and escalation authority.
• Synchronise clocks and centralise protected audit records.
• Preserve evidence before destructive remediation where practicable.
• Record hashes, transfers, decisions and approvals.
• Restore into a clean environment and validate application behaviour.
• Conduct lessons-learned reviews and track corrective actions.

Chapter Summary

Secure database resilience combines recoverable backups, realistic recovery objectives and disciplined incident response. RPO defines the acceptable age of recovered data, while RTO defines the acceptable time to restore service. Full, differential, incremental and transaction-log backups serve different operational needs, and replication must not be mistaken for independent recovery protection. The 3-2-1-1-0 approach, immutability, isolation, encryption, separate administration and regular restoration testing reduce ransomware risk. Incident response proceeds through preparation, detection, containment, investigation, eradication, recovery and improvement. Throughout the process, evidence must be collected lawfully, protected from alteration and documented through chain of custody. Effective programmes align technical controls with business impact, privacy obligations, governance and tested human procedures.

Key Terms

Backup: A separate recoverable copy of data or configuration.

Business continuity: Capability to continue critical business processes during disruption.

Chain of custody: Documented history of evidence handling and transfer.

Containment: Actions that limit the spread or impact of an incident.

Differential backup: Backup of changes since the latest full backup.

Disaster recovery: Coordinated restoration of technology services after disruption.

Eradication: Removal of malicious access, software and persistence.

Full backup: Copy of all selected database contents.

Incident response: Organised management of a security event from detection through lessons learned.

Immutable backup: Backup protected against alteration or deletion for a defined period.

Incremental backup: Backup of changes since the most recent applicable backup.

Recovery point objective (RPO): Maximum acceptable period of data loss.

Recovery time objective (RTO): Maximum acceptable time to restore required service.

Replication: Maintenance of a copy updated from a source system.

Restoration: Technical process of returning data or services from a recovery source.

Snapshot: Point-in-time representation of data, often dependent on its storage platform.

Transaction log: Ordered record of database changes used for recovery.

Write-ahead log: Change record written before corresponding data pages, supporting durability and recovery.

Review Questions

Short Answer Questions

1. Distinguish RPO from RTO.
2. Why is replication not necessarily a backup?
3. State the purpose of an immutable backup.
4. Name four phases of the incident-response lifecycle.
5. What information should a chain-of-custody record contain?

Long Answer Questions

1. Explain how a database backup strategy can be designed from business impact analysis.
2. Compare full, differential, incremental and transaction-log backups.
3. Describe a secure restoration procedure following ransomware.
4. Explain how evidence should be collected and protected during a database incident.
5. Discuss the governance and privacy issues associated with long-term backup retention.

Analytical / Application Questions

1. A failure occurs at 18:00, the latest verified recovery point is 17:42, and service returns at 21:10. Determine whether an RPO of 30 minutes and an RTO of three hours are met, showing your reasoning.
2. A company relies on a continuously updated reporting replica and has no offline or immutable copy. Analyse its exposure to ransomware and propose a revised architecture.
3. During an investigation, management asks the administrator to rebuild and wipe the compromised database immediately. Evaluate the operational and evidential consequences and recommend a controlled response.

Multiple Choice Questions with Answers

Question 1

Which statement best defines RPO?

A. The maximum time allowed to restore service

B. The maximum acceptable age of recovered data

C. The number of backup copies required

D. The time needed to investigate an incident

Correct Answer: B. The maximum acceptable age of recovered data

Question 2

A database fails at 10:00 and the latest verified recovery point is 09:45. What is the data-loss interval?

A. 10 minutes

B. 15 minutes

C. 45 minutes

D. 55 minutes

Correct Answer: B. 15 minutes

Question 3

Why can replication fail as a ransomware recovery control?

A. Replicas cannot contain database tables

B. Replication always stops transaction logging

C. Malicious changes may be replicated to the copy

D. Replicas cannot be encrypted

Correct Answer: C. Malicious changes may be replicated to the copy

Question 4

Which control most directly prevents premature alteration of a backup?

A. Retention lock or immutability

B. A larger production server

C. A read-only reporting query

D. A shorter database password

Correct Answer: A. Retention lock or immutability

Question 5

Which incident-response phase removes malicious persistence and unauthorised accounts?

A. Preparation

B. Containment

C. Eradication

D. Recovery testing

Correct Answer: C. Eradication

Question 6

What is the primary purpose of a cryptographic hash in evidence handling?

A. To prove who committed the incident

B. To detect whether the item changed

C. To encrypt the evidence permanently

D. To identify the legal jurisdiction

Correct Answer: B. To detect whether the item changed

Question 7

Which backup type generally contains changes since the latest full backup?

A. Differential backup

B. Incremental backup only

C. Replica snapshot only

D. Evidence image

Correct Answer: A. Differential backup

Question 8

A service fails at 08:00 and returns at 10:30. Which RTO requirement is met?

A. An RTO of two hours

B. An RTO of 90 minutes

C. An RTO of three hours

D. An RTO of 120 minutes exactly

Correct Answer: C. An RTO of three hours

Question 9

Which action is most appropriate before restoring a ransomware-affected database?

A. Restore directly into the infected production network

B. Use a clean, isolated recovery environment

C. Delete all logs to save storage

D. Reconnect the replica immediately

Correct Answer: B. Use a clean, isolated recovery environment

Question 10

Which practice best supports chain of custody?

A. Allowing anyone on the team to access evidence without records

B. Analysing the only original copy directly

C. Recording collection, transfers, hashes and access

D. Compressing evidence without documenting the tool

Correct Answer: C. Recording collection, transfers, hashes and access