# How Should Database Backup, Restore and Point-in-Time Recovery Be Planned? **URL:** https://securesys.com.tr/en/learning/database-security/database-backup-restore-and-pitr ![How Should Database Backup, Restore and Point-in-Time Recovery Be Planned?](/images/bilgi-merkezi/covers/cover-veritabani-10.webp) A database being backed up does not mean that database can genuinely be protected. The real question is this: #### Can we come back if we need to? A database backup system can look technically successful. However; the backup can be corrupt, the transaction log can be incomplete, the encryption key may not be found, the restore process may not have been tested, the dependencies may not be ready or the organization may be unable to return to the time it needs. For this reason a database backup strategy must answer not only: **“Are backups being taken?”** as the question; **“To which point, with how much data loss and within what time can we return?”** but these questions as well. The core components of the corporate database recovery approach are these: #### Full Backup #### Incremental / Differential Backup #### Transaction Log / WAL / Archive Log #### Point-in-Time Recovery – PITR #### RPO #### RTO #### Backup Encryption #### Immutable Backup #### Restore Testing and **Disaster Recovery** When one of these components is missing, the backup process can look as if it works on paper yet fall short at the real moment of crisis. ### What Is Database Backup? Database Backup is the creation of a separate copy of the data in the database and, where needed, of the structures required for recovery. The purpose is; data loss, corruption, human error, hardware failure, ransomware, an application error to be able to bring the database back after events such as these. ### What Is the Difference Between Backup and Restore? Backup: creates a copy of the data. Restore: makes the database usable again from that copy. Real security is measured by restore success. ### If the Backup Succeeds, Does the Restore Succeed Too? No. This is one of the most frequently made mistakes. The backup job can show: #### Success as its result. However, the file; corrupt, incomplete, encrypted but with the key lost, incompatible can be any of these. For this reason a restore test is necessary. ### What Is Recovery? Recovery is not merely the restoration of the database's files but bringing it into a consistent and usable state. ### Are Restore and Recovery the Same? Not exactly. Restore puts the backup file back. Recovery, meanwhile, can cover applying transaction or log information to bring the database into a consistent state. ### Why Must the Database Backup Strategy Start from the Business Requirement? Not every database is at the same criticality level. For example: a Product Catalog DB and a Banking Transaction DB may not have the same RPO and RTO targets. ### How Is a Critical Database Determined? With a Business Impact Analysis; the business process, revenue impact, customer impact, regulatory impact, operational dependency can be assessed. ### What Is RPO? RPO is: #### Recovery Point Objective which expresses the maximum acceptable data loss. For example, if: RPO = 15 minutes then the organization accepts losing at most 15 minutes of data. ### What Is RTO? RTO is: #### Recovery Time Objective which expresses within what time the system must be running again. For example, if: RTO = 2 hours then the database must be usable within two hours at the latest. ### Is There a Relationship Between RPO and Backup Frequency? Yes. The RPO of a system taking one backup a day cannot in most cases be a few minutes. ### Does an Hourly Backup Mean an RPO of 1 Hour? Approximately it can be, but if transaction logs or continuous log shipping are used, a lower RPO can be achieved. ### Does RTO Depend Only on the Restore Duration? No. RTO; storage, network, server provisioning, DNS, application, identity, key management is affected by dependencies such as these. ### What Is a Full Backup? It is a copy of the entire database at a specific point. ### What Is the Advantage of a Full Backup? The restore process is generally simpler. ### What Is the Disadvantage of a Full Backup? On large databases; storage, network, the backup window can be high. ### What Is a Differential Backup? It is the backup of the data changed since the last full backup. ### How Does a Differential Backup Restore Work? Generally; the full backup the latest differential backup are used. ### What Is an Incremental Backup? It is the backup of the blocks or data changed since the last backup. ### What Is the Advantage of an Incremental Backup? The backup duration and storage consumption can be reduced. ### What Is the Disadvantage of an Incremental Backup? The restore chain can be more complex. ### What Is a Transaction Log Backup? It is the additional backup of the log records of the transactions taking place on the database. It is important particularly for point-in-time recovery. ### Why Is MSSQL Transaction Log Backup Critical? In Microsoft SQL Server, when the appropriate recovery model is used, the database can be brought back to a specific time with transaction log backups. ### What Is PostgreSQL WAL? WAL is: #### Write-Ahead Logging the mechanism helping PostgreSQL provide consistency and recovery by writing changes to the log first. ### Can WAL Be Used for Backup? Yes. Archived WAL records can be used together with a base backup for Point-in-Time Recovery. ### What Is an Oracle Archive Log? They are the archived copies of Oracle redo log information. They carry critical importance for recovery. ### Are PostgreSQL WAL, MSSQL Transaction Log and Oracle Archive Log the Same Thing? Their technical implementations differ. However, from a recovery perspective the common logic is: **keeping a record of database changes over time.** ### What Is Point-in-Time Recovery – PITR? It is bringing the database back to a specific date and time. ### Why Is PITR Necessary? For example, a user deleted a critical table by mistake at 14:02. The database can be returned to the; 14:01:59 point before the event. ### Is PITR Different from a Snapshot? Yes. A snapshot returns to specific fixed points. PITR can provide far more precise recovery points with the transaction log chain. ### Is PITR Automatically Available on Every Database? No. The correct log, archive and backup configuration is required. ### What Is the Most Critical Thing for PITR? It is the backup chain being complete. If one log record is lost, the recovery chain can break. ### What Is a Log Chain Break? It is the interruption of the transaction log sequence. In that case recovery may not be possible for anything after a certain point. ### Why Can the MSSQL Log Chain Break? Wrong backup or recovery operations can affect the chain. For this reason backup operations must be controlled. ### What Happens If the PostgreSQL WAL Archive Is Incomplete? Because of a missing WAL segment the target recovery point may not be reachable. ### What Happens If the Oracle Archive Log Is Incomplete? Recovery can stop at a certain point. ### How Should the Database Backup Schedule Be Designed? It must be designed according to the business requirement. For example: Weekly Full Daily Differential 15-minute Transaction Log a combination along these lines can be used. ### Should the Same Backup Schedule Be Used for Every Database? No. It can differ according to criticality and workload. ### Can a Transaction-Heavy Database Need More Frequent Log Backups? Yes. For a lower RPO, more frequent log capture may be needed. ### Is the Same Strategy Required for a Read-Only Database? No. If the change frequency is low, a different backup approach can be applied. ### What Is a Backup Window? It is the time interval in which the backup operation can run on the production system. ### Does Backup Affect Production Performance? Yes. IO, CPU, network it can create load on these. ### What Is Backup Throttling? It is the mechanism aimed at limiting the resource consumption of the backup operation. ### What Is an Online Backup? It is the backup taken while the database keeps running. ### What Is an Offline Backup? It is the backup taken after the database has been stopped. ### Which Is Used on Production Systems? On 24x7 systems an online backup is mostly required. ### What Is an Application-Consistent Backup? It is the backup providing a consistent recovery point from the application's and the database's perspective. ### What Is a Crash-Consistent Backup? It is a storage snapshot taken in a way similar to the system's sudden shutdown state. Consistency is sought through the database recovery mechanism. ### Is a Crash-Consistent Backup Enough? It varies according to the platform and the workload. On critical databases an application/database-aware backup can be preferred. ### What Is a Database Snapshot? It is the image of the database disks at a specific time at the storage or cloud level. ### Is a Snapshot a Backup? On its own it must not take the place of a backup. It can be tied to the same storage system. ### Can a Snapshot Be Lost in a Storage Failure? Yes. If it is inside the same storage failure domain, the snapshot can be affected too. ### Is a Snapshot Safe Against Ransomware? Not always. If the attacker gains storage admin privileges they can delete the snapshots. ### What Is an Immutable Backup? It is a backup that cannot be modified or deleted for a defined retention period. ### Should Database Backups Be Immutable? On critical databases it can be strongly evaluated. ### Why Is Immutable Backup Important? Ransomware attackers can target backups so as to destroy the recovery capability. ### Can WORM Be Used for Database Backup? Yes. The Write Once Read Many approach can provide unchangeable backup retention. ### What Is Air-Gap Database Backup? It is the backup being separated physically or logically from the production systems. ### Are Air Gap and Immutable the Same? No. Immutable: It cannot be modified. Air Gap: It is isolated. The two can be used together. ### Can the 3-2-1 Backup Rule Be Applied to Databases? Yes. The basic approach is: 3 copies of the data, 2 different media, 1 offsite copy in this form. ### What Is the Modern 3-2-1-1-0 Approach? In addition it targets; 1 offline/immutable copy and 0 unverified recovery errors as its approach. ### Why Is Database Backup Encryption Critical? A backup contains a full copy of the production data. For this reason the theft of a backup can be a data breach. ### Is Backup Encryption at Rest Necessary? For sensitive databases, yes. ### Should Backup Transfer Use TLS? Backup traffic carried over the network must be encrypted too. ### Where Should the Backup Encryption Key Be Kept? It must not be kept in the clear in the same place as the backup. ### Why Is Key Management Critical for Recovery? Even if the backup file is intact, a restore may not be possible if the encryption key is lost. ### What Is a Database Restore Test? It is the verification that a database can genuinely be created and used from the backup. ### How Often Should a Restore Test Be Performed? It must be done periodically according to criticality. A backup never restored for years must not be considered trustworthy. ### Does a Restore Test Mean Only “The Database Opened”? No. Application connectivity and data integrity must be verified too. ### What Should Restore Validation Include? For example: Is the database online? Are the expected tables there? Is the row count sensible? Can the application connect? Is transaction consistency correct? ### Can a Checksum Be Used? Integrity checks can be used to verify whether the backup file has become corrupt. ### Does Backup Verification Replace a Restore? No. The file looking healthy does not guarantee that a real restore will work. ### What Is an Isolated Restore Test? It is the backup being restored in a test or recovery environment separate from production. ### What Is a Clean Room? It is the isolated environment where a suspicious or post-attack backup is examined and restored safely. ### Should a Backup Be Restored Directly to Production After Ransomware? Not always. It must be verified that the backup is clean and carries no persistence. ### Can There Be Malware Inside a Database Backup? Even if a database backup does not contain classic executable malware, content added by the attacker such as; malicious data, a script, a stored procedure, a scheduled job may be present. ### What Is a Clean Restore Point? It is the recovery point taken before the attacker's activity and verified as safe. ### What Is Dwell Time? It is the period the attacker remains inside the system undetected. ### How Does Dwell Time Affect Backup Retention? If the attacker stayed in the system for 40 days, only the last 7 days of backups may be insufficient. ### What Is Backup Retention? It determines how long backup copies will be kept. ### How Should Retention Be Determined? Business, legal, security, storage cost must be assessed together. ### What Is GFS Retention? It is the daily, weekly and monthly backup retention model such as Grandfather-Father-Son. ### Are Database Backup Retention and Log Retention the Same? No. Recovery logs and security audit logs serve different purposes. ### What Is the Database Backup Catalog? It is the metadata system showing which backup was taken when and where it resides. ### Why Is the Backup Catalog Critical? It helps determine which chain will be used for the restore. ### Can the Backup Catalog Be a Ransomware Target? Yes. For this reason the backup management server must be protected too. ### Should the Backup Administrator Be Separate? In critical environments, separating them from the production DBA can reduce risk. ### How Is Separation of Duties Applied for Backup? The DBA manages the database. The backup admin manages the backup infrastructure. The security team audits. ### Should the DBA Be Able to Delete All the Backups? Where possible, no. In a production compromise the attacker must not be able to destroy the recovery layer as well with DBA credentials. ### What Is a Backup Repository? It is the storage area where database backups are kept. ### Should the Backup Repository Be Exposed to the Internet? No. ### Is Network Segmentation Necessary for the Backup Repository? Yes. The backup network must be separated as far as possible. ### Should the Backup Server Be a Domain Admin? Generally no. The backup identity architecture can be separated from the production identity. ### Is MFA Necessary for the Backup Console? On critical backup management accounts it is strongly recommended. ### Can PAM Be Used for the Backup Administrator? Yes. ### What Is Database Backup Monitoring? It is the monitoring of backup jobs' metrics such as; success, failure, duration, size, age as its scope. ### Should There Be a Backup Failure Alarm? Absolutely. ### Why Is the Age of the Last Successful Backup a KPI? If a database has taken no backup for 72 hours, there is a recovery risk even though the system looks like it is running. ### What Is a Backup Size Anomaly? A backup that is normally 500 GB suddenly being 10 GB can be an indicator of a serious problem. ### What Is a Backup Duration Anomaly? A backup much shorter or longer than normal; configuration, storage, network can indicate a problem in these. ### What Is the Backup Success Rate KPI? It is the rate at which planned backups complete successfully. ### Is Restore Success Rate More Important? Yes. Because the real aim is to be able to come back. ### What Is the Restore Time KPI? It is the time taken to make the database usable from the backup. ### What Is Actual RTO? It is the recovery time genuinely measured during a test. ### What Is the Difference Between Declared RTO and Actual RTO? On paper the RTO can be 1 hour. If the restore test takes 6 hours, the real capacity is not 1 hour. ### How Is Actual RPO Measured? The last usable transaction time after recovery can be compared with the incident time. ### What Is a Database Restore Runbook? It is the operations document defining the recovery process step by step. ### Why Is a Runbook Necessary? It prevents teams from relying on memory during a crisis. ### What Should a Database Recovery Runbook Include? For example: Backup location Responsible team Key access Restore order Log application Validation Application handover ### Should Credentials Be Written in the Runbook? No. A secure vault reference can be used. ### Why Is the Restore Order Important? Some databases can be dependent on each other. ### What Is Dependency Mapping? It determines which; application, identity, DNS, storage, network components the database depends on. ### If the Database Runs but the Application Does Not Open, Is Recovery Complete? No. If the business service is not usable, the real recovery is not complete. ### What Is Database Disaster Recovery? It is running the service again in an alternative environment when the main database infrastructure cannot be used. ### Are Backup and DR the Same? No. Backup provides a copy of the data. DR, meanwhile, aims to run the whole service in an alternative environment. ### Does Replication Replace Backup? No. A wrong DELETE operation can be replicated to the replica as well. ### Are HA and Backup the Same? No. High Availability provides continuity against hardware or node failure. Backup provides historical recovery. ### What Is an Active-Passive Database? While one primary database runs, a secondary can be kept on standby. ### What Is an Active-Active Database? More than one node can serve actively at the same time. ### Does HA Make RPO Zero? Not in every architecture. It depends on the replication method. ### What Is Synchronous Replication? It is the model in which the secondary node's confirmation is also awaited for the transaction to complete. ### What Is Asynchronous Replication? After the primary transaction completes it can be sent to the secondary. It provides low latency but there can be a data loss risk. ### What Is Replication Lag? It is the delay between the primary and the replica. ### Should Replication Lag Be Monitored? Yes. It can lead to more data loss than expected at the moment of DR. ### What Is Failover? It is the secondary becoming active when the primary database fails. ### What Is Failback? It is the service being moved back to the original environment when the main system returns. ### Should Failover Be Tested? Yes. An HA design never tested is not trustworthy. ### What Is a Planned Failover? It is a failover performed for controlled maintenance or testing. ### What Is an Unplanned Failover? It is the automatic or manual failover after an unexpected failure. ### What Is Split-Brain? It is the risk of data inconsistency as a result of more than one node behaving as primary at the same time. ### How Often Should a Database DR Test Be Performed? It must be done periodically according to criticality and the business continuity plan. ### Is a Tabletop Test Enough? On its own, no. Real technical restore and failover tests must be performed too. ### What Is a Full DR Drill? It is the end-to-end recovery test covering application, database, network and user access. ### The PostgreSQL Backup and Recovery Approach In PostgreSQL, fundamentally; logical backup, physical/base backup, WAL archiving, PITR approaches such as these can be evaluated. ### What Is a PostgreSQL Logical Backup? It is exporting the schema and data in a logical format. ### What Is the Advantage of a Logical Backup? It can be useful for table- or object-level recovery and for migration. ### Is a Logical Backup Enough for a Large Database? On its own it may not always be suitable. The recovery time can be long. ### What Is a PostgreSQL Physical Backup? It is taking the database cluster files at the physical level. ### What Is the PostgreSQL Base Backup + WAL Model? The base backup provides a specific starting point. WAL records apply the later transactions to provide recovery to the target time. ### The PostgreSQL PITR Logic In summary: Base Backup Archived WAL ↓ Target Time ↓ Recovered Database ### Why Is PostgreSQL WAL Archive Monitoring Critical? If archiving fails continuously, the PITR capability can be lost. ### The MSSQL Backup and Recovery Approach In MSSQL the; Full Backup, Differential Backup, Transaction Log Backup combination is widely used. ### Why Is the MSSQL Recovery Model Important? It determines the transaction log backup and PITR behaviour. ### What Is the MSSQL Full Recovery Model? With an appropriate log backup process it provides point-in-time recovery. ### What Is the MSSQL Simple Recovery Model? Transaction log management is simpler but it does not provide the same PITR capability. ### Should Every MSSQL Database Be Full Recovery? No. It must be chosen according to the business requirement. ### What Should the MSSQL Transaction Log Backup Schedule Be? It must be determined according to the target RPO. ### The MSSQL Restore Chain For example: Full ↓ Differential ↓ Transaction Logs ↓ Target Time it can be along these lines. ### What Is a Tail-Log Backup? In appropriate situations it can help reduce data loss by capturing the not-yet-backed-up portion of the transaction log after a failure. ### The Oracle Backup and Recovery Approach In Oracle environments; RMAN, backup sets, incremental backup, archive logs mechanisms such as these can be used. ### What Is RMAN? Oracle Recovery Manager is the core management tool used for Oracle database backup and recovery operations. ### What Is Oracle ARCHIVELOG Mode? By enabling online redo logs to be archived it supports more advanced recovery scenarios. ### What Is Oracle NOARCHIVELOG Mode? Because archive logs are not kept, the recovery options are more limited. ### Can Oracle PITR Be Done? With the right backup and archive log structure, yes. ### Can Oracle RMAN Backup Encryption Be Used? On the appropriate platform and configuration an encrypted backup can be applied. ### Why Is the Oracle Control File Important for Recovery? It contains critical metadata about the database structure. It must be taken into account separately in the recovery strategy. ### The Common Recovery Logic of PostgreSQL, MSSQL and Oracle Even though the platforms differ, the core model is the same: #### Base/Full Backup #### Change/Transaction Logs #### Recovery Target = #### Point-in-Time Recovery ### What Is Cloud Managed Database Backup? It is the cloud provider's database service offering native backup and PITR capabilities. ### If We Use Managed Backup, Is the Responsibility Over? No. The customer; retention, restore testing, access control, encryption, the cross-region strategy must manage these subjects. ### Is Cloud Database Automated Backup Reliable? Technically it can be strong. However, the restore must be tested. ### What Is Cloud PITR? It is a managed database being able to recover to a specific time through its transaction log or engine mechanism. ### Why Is Cross-Region Backup Used? It provides additional protection against region-level disaster risk. ### What Is Cross-Account Backup? It is the backup being kept in a different security boundary from the production cloud account. ### Does Cross-Account Backup Reduce Ransomware Risk? Yes. It can make it harder for the same credential compromise to delete both production and the backup. ### Can Cloud Backup Object Lock Be Used? On appropriate object storage services, immutable retention can be provided. ### How Should the Database Backup Identity Be Protected? The backup service account must run with minimum privileges. ### Should the Backup Service Account Be the Production DBA? As far as possible, no. ### Why Is Backup Credential Rotation Important? It reduces the risk of static long-lived credentials. ### What Happens If the Backup Secret Is Lost? The backup jobs can stop. For this reason secret lifecycle and monitoring are required. ### Is a Backup Job Failure a Security Event? A long-lasting or unexplained failure must be assessed as a security risk. ### Can the Attacker Turn Off the Backup Job? If they obtain privileged access, yes. For this reason backup policy changes must be audited. ### Should Backup Deletion Generate an Alarm? The mass deletion of critical backups can be a high-severity security event. ### Should a Retention Policy Change Be Monitored? Yes. Retention suddenly dropping from 90 days to 1 day can be suspicious. ### Should Immutable Mode Disabled Be an Alarm? Yes. ### Should Backup Repository Authentication Failures Be Monitored? Yes. They can be an indicator of brute force or credential abuse. ### Should the Backup Management Server Be Protected with EDR? With compatibility verified, it must be protected with critical security controls. ### Should the Backup System Send Logs to SIEM? Critical events can be sent. ### SIEM Use Cases for Database Backup Examples: Backup Job Disabled Mass Backup Deletion Retention Changed New Backup Administrator Unexpected Restore Immutable Policy Disabled ### Why Can a Restore Operation Be a Security Event? The attacker can restore the backup onto a different system and read the data. ### How Can an Unauthorized Restore Be Detected? Backup management and database audit events can be monitored. ### Is Restore Approval Necessary? For critical production data a controlled process can be used. ### Database Backup and KVKK Personal data inside a backup is personal data too. For this reason the access, retention and security of backup files must be addressed separately. ### Does Encrypting the Backup Automatically Provide KVKK Compliance? No. Other controls such as access control, retention, secure deletion and incident management are also required. ### Database Backup and ISO/IEC 27001 Backup, availability, information security continuity and recovery processes must be managed on a risk basis. ### Database Recovery and ISO 22301 From a business continuity perspective, the RPO and RTO targets of critical databases can be related to the business impact analysis. ### Database Backup and PCI DSS Backups containing card data must be treated as sensitively as production data and protected strictly. ### Database Backup Risk Assessment For every critical database these risks must be assessed: Backup failure Restore failure Backup deletion Backup theft Encryption key loss Log chain break Ransomware Storage failure ### What Is a Backup Single Point of Failure? It is all the backups depending on a single storage system or a single credential. ### Is a Single Backup Repository Risky? Yes. A repository failure can affect the entire recovery capability. ### Should Backup Copies Be in Different Failure Domains? On critical systems, yes. ### Can a Recovery Dependency Be a Single Point of Failure? Yes. For example, if all backups are encrypted and tied to a single KMS, a KMS failure can halt the recovery. ### Database Recovery Test Scenarios The organization must test different scenarios. For example: Single table loss Full database loss Storage failure Ransomware Regional disaster Human error ### What Is Single Table Recovery? It is bringing back a specific table or object instead of the whole database. ### Does Every Platform Support Single Table Restore? The method and capability can vary by DBMS. In some cases the database is restored into a separate environment and the table is extracted. ### The Accidental DELETE Scenario A user deletes critical data at 14:00. The recovery options: PITR or alternate restore + data extraction can be either. ### The Data Corruption Scenario If the database experiences physical or logical corruption, an integrity check and a clean backup are needed. ### What Is Logical Corruption? The database technically runs but the data inside it is wrong or inconsistent. ### Does the Backup Copy Logical Corruption Too? Yes. For this reason long retention and multiple restore points matter. ### The Ransomware Scenario The primary database and the online backups can be affected. An immutable/offline backup becomes critical for recovery. ### The Regional Disaster Scenario The primary data centre can become entirely unreachable. An offsite or cloud DR is required. ### Should Backup Test Results Be Documented? Yes. ### What Should the Restore Test Report Include? Backup ID Restore Start Restore Finish Result Actual RTO Recovered Point Actual RPO Errors ### What Does a Failed Restore Test Mean? It is a critical finding for the backup system. ### Should a Restore Test Failure Be Resolved Quickly? Yes. Because recovery may not be possible at the moment of a real incident. ### Database Backup KPIs For example: Backup Success Rate Restore Success Rate Last Successful Backup Age Actual RPO Actual RTO Immutable Backup Coverage Encrypted Backup Coverage ### If the Backup Success Rate Is 100%, Is the System Secure? No. If restore success is unknown it is an incomplete metric. ### What Is Restore Test Coverage? It measures how many of the databases went through a restore test within a given period. ### What Is PITR Coverage? It can show what percentage of the critical databases have point-in-time recovery capability. ### What Is Immutable Backup Coverage? It shows what percentage of the critical database backups are protected as immutable. ### What Should the Database Backup Dashboard Show? Failed Jobs Last Backup Last Restore Test RPO Status RTO Status Immutable Coverage Encryption Coverage ### What Is Backup Aging? It is the time elapsed since the last successful backup. ### What Is a Backup Gap? It is a longer gap than there should be between the expected backups. ### What Is an RPO Breach? If the real backup/recovery position cannot meet the targeted RPO, a breach occurs. ### A Sample RPO Breach The RPO target: 15 minutes. The last log backup: 2 hours ago. In reality the RPO target is not being met. ### What Is an RTO Breach? It is the restore operation taking longer than the target period. ### The Database Recovery Maturity Model #### Level 1 – There Is a Backup The backup jobs are running. #### Level 2 – There Is Monitoring Backup successes are monitored. #### Level 3 – There Is Restore Testing Periodic restores are verified. #### Level 4 – PITR + Immutable There is advanced recovery and ransomware resilience. #### Level 5 – Cyber Recovery Clean room, automated orchestration and regular DR exercises are applied. ### The Most Common Mistakes in Database Backup The mistakes frequently seen in organizations are as follows: - Taking backups but not testing the restore - Mistaking a snapshot for a backup - Mistaking replication for a backup - Not defining RPO and RTO - Not taking transaction log backups - Not monitoring the WAL/archive log chain - Keeping the backup on the same storage as production - Storing the backup unencrypted - Not testing encryption key recovery - Not using immutable backup - Making the backup administrator over-privileged - Not monitoring backup deletion events - Not testing the DR databases - Not performing a real restore for years - Setting backup retention without taking dwell time into account ### Database Backup Checklist A corporate database backup checklist: - Has criticality been determined? - Has the RPO been defined? - Has the RTO been defined? - Is there a full backup? - Is there a log/WAL/archive backup? - Is PITR possible? - Is the backup in a different failure domain? - Is the backup encrypted? - Is there an immutable copy? - Is there an offsite copy? - Is backup access minimal? - Is there MFA? - Is backup deletion audited? - Are restore tests performed? - Has encryption key recovery been tested? - Is the actual RPO measured? - Is the actual RTO measured? - Has DR failover been tested? ### PostgreSQL Backup Checklist - Is the base backup regular? - Is WAL archiving working? - Is there an archive failure alarm? - Has PITR been tested? - Is logical backup used according to need? - Is the backup encrypted? - Has a restore test been performed? ### MSSQL Backup Checklist - Is the recovery model correct? - Is there a full backup? - Is the differential backup suited to the need? - Is the transaction log backup regular? - Is the log chain healthy? - Is backup encryption in use? - Has PITR been tested? - Has the restore time been measured? ### Oracle Backup Checklist - Is the RMAN backup healthy? - Is ARCHIVELOG active as needed? - Are the archive logs protected? - Is there a control file recovery plan? - Is backup encryption applied? - Has recovery been tested? - Has the PITR scenario been verified? ### Database Recovery Questions Management Should Ask Management must be able to get answers to these questions: What is the RPO of our critical databases? What is their RTO? When was the last successful backup? When was the last real restore test performed? Are our backups immutable? Are our backups encrypted? Is the transaction log/WAL/archive chain healthy? In the case of ransomware, do we have a clean backup? Within what time can we move the database to another data centre? If the encryption key is lost, can we perform a recovery? ### Frequently Asked Questions #### What is database backup? It is the secure copy created so the database can be brought back after data loss or a system failure. #### What is restore? It is making the database usable again from the backup. #### What is Point-in-Time Recovery? It is returning the database to a specific date and time. #### What is RPO? It is the maximum data loss period the organization can accept. #### What is RTO? It is the maximum acceptable return time of the system. #### Does a snapshot replace a backup? On its own, generally no. It can be exposed to the same storage failure or ransomware impact. #### Does replication replace a backup? No. A wrong or malicious data change can be carried to the replica as well. #### What is PostgreSQL WAL for? It records transaction changes and, with the appropriate configuration, can be used for PITR. #### Why is an MSSQL transaction log backup important? It is an important recovery component for point-in-time recovery and low RPO targets. #### Why is an Oracle archive log necessary? They are the important log records enabling database changes to be reapplied during recovery. #### What is an immutable backup? It is a backup that cannot be modified or deleted for a defined period. #### If the backup succeeds, is a restore test necessary? Absolutely. A successful backup job does not guarantee real recovery success. ### Conclusion: The Value of a Backup Is Understood Not on the Day It Is Taken but on the Day You Come Back The most frequently made mistake in database backup processes is treating the backup job success rate as the real security metric. Yet the real aim is not to take backups. The aim is: **to be able to bring the database back.** Real database recovery capability requires these components to work together: #### Backup #### Transaction Log / WAL / Archive Log #### PITR #### Encryption #### Immutable Storage #### Restore Testing #### RPO / RTO The value of a backup is understood only when it is restored. For this reason organizations, instead of the: **“Are we taking backups?”** question, need to ask these questions: **“Up to which time can we return?”** **“How much data can we lose?”** **“Within what time can we get back up?”** **“Is our backup protected from the attacker?”** **“Is our encryption key genuinely usable?”** **“When did our last restore test succeed?”** WAL for PostgreSQL, the Transaction Log for MSSQL, the Archive Log for Oracle although they are different technologies, they serve the same aim: **enabling the database to be returned not only to the last backup point but, as far as possible, to the exact time needed.** Real recovery maturity must be measured not by: #### Backup Success but by; #### Verified Recoverability as the standard. That is: **verified ability to come back.**