-
Key Takeaways
-
Version and Environment Scope
-
What Is Point-in-Time Recovery?
-
Why PITR Is the Right Tool for Logical Corruption
-
Prerequisites: What Must Already Exist
-
Step 1: Stop the Damage and Preserve Evidence
-
Step 2: Choose the Target Time Correctly
-
Step 3: Restore Into a Separate Instance First
-
Workflow: PITR Commands by Engine
-
Step 4: Validate Before Switching Over
-
Step 5: Bring the Data Back and Close the Gap
-
Decision Matrix: Which Recovery Approach to Use
-
Best Practices
-
Operationalizing PITR Across Many Databases
-
FAQs
-
Conclusion
To restore a database to a specific point in time (PITR), restore the last good full backup taken before the corruption began, then replay transaction logs (WAL, binary logs, transaction logs or archived redo logs) up to the last moment the data was still correct. Do it on a separate instance first, validate the data, and only then decide whether to switch over or merge the recovered data back. PITR only works if log backups were already running before the incident. Database point-in-time recovery after logical corruption relies on a verified base backup and a continuous transaction log chain.
Key Takeaways
The recovery target must be the last known good time, not the time the problem was discovered. Corruption often starts earlier than detection.
PITR needs two assets: a base backup older than the corruption, and an unbroken chain of log files from that backup to the target time.
Restoring to a new instance or database first is safer than rolling back in place, because an in-place rollback discards every legitimate transaction after the target.
Physical corruption (damaged pages, failed storage) and logical corruption (bad updates, deletes, ransomware encryption) need different handling. PITR mainly addresses logical corruption.
Syntax and limits differ by engine: PostgreSQL, MySQL, SQL Server, and Oracle each use different log mechanisms and recovery commands.
Version and Environment Scope
This guide applies to self-managed PostgreSQL, MySQL, SQL Server, and Oracle databases that have a valid full backup and a continuous log chain. Cloud-managed databases such as Amazon RDS and Azure SQL Database may require restoring to a new instance or database, and the commands below do not apply to them.
Environment | Applies? | Condition to check |
Self-managed PostgreSQL | Yes | A base backup and WAL archiving. The recovery.signal method shown here applies to PostgreSQL 12 and later; earlier versions use a different recovery configuration, so check your version's documentation. |
Self-managed MySQL | Yes | Binary logging enabled and binary logs retained. Defaults depend on version and configuration. |
Self-managed SQL Server | Yes | Full or bulk-logged recovery model. The simple recovery model does not support log backups, and bulk-logged limits point-in-time restore for logs containing bulk-logged operations. |
Self-managed Oracle | Yes | ARCHIVELOG mode and valid archived redo logs. Flashback options depend on configuration. |
Amazon RDS, Azure SQL Database | Concept only | These services typically restore to a new instance or database, with provider-defined retention and tools. Use the provider's documented method. |
Any database without log backups | Partial | Recovery is limited to the time of the full, incremental, or differential backup. |
Physical storage failure | Partial | Restore onto healthy storage first; PITR then handles logical recovery. |
Always verify the behavior against the official documentation for your database version, recovery model, and deployment type before running recovery commands.
What Is Point-in-Time Recovery?
Point-in-time recovery returns a database to its state at a chosen moment by combining a full backup with the recorded stream of later changes.
A backup alone captures one moment. A transaction log records every change after that moment. Replaying the log forward from the backup, and stopping at a chosen time, reconstructs the database at that time.
Engines use different names for the log:
Engine | Change log | Official reference |
PostgreSQL | Write-ahead log (WAL), archived | |
MySQL | Binary log | |
SQL Server | Transaction log (full or bulk-logged recovery model) | |
Oracle | Redo log, archived (ARCHIVELOG mode) |
Practical implication: if the log is not being captured, PITR is not available for that database, whatever product you use later.
Why PITR Is the Right Tool for Logical Corruption
Restoring only the latest backup returns data to the backup time and loses everything since. PITR narrows that loss to the minutes between the last good moment and the target.
Common triggers where PITR applies:
An UPDATE or DELETE run without a correct WHERE clause.
A faulty application release or migration script that rewrote data.
A dropped or truncated table.
Ransomware or malicious changes that modified database contents.
Where PITR is not enough on its own:
Physical corruption such as damaged data pages or failing disks. The goal is to recover onto healthy storage, and to confirm the base backup itself is clean.
Loss of the log chain. A missing log file stops replay at the gap. Recovery can only reach the point before the gap.
Prerequisites: What Must Already Exist
You need a clean full backup, continuous log capture, and a known time window for the incident.
Checklist:
1. A full backup that predates the corruption and has been verified.
2. Log archiving or log backups enabled before the incident.
3. An unbroken log chain from the full backup to your target time.
4. Spare capacity (a second server, instance, or database name) to restore into.
5. Synchronized, documented time zones across the database server, application servers, and backup server.
6. Enough free space for the base backup plus the replayed log volume.
Engine-specific switches:
SQL Server: log backups require the full or bulk-logged recovery model. According to Microsoft documentation, the simple recovery model does not support log backups, and the bulk-logged recovery model limits point-in-time restore for log backups that contain bulk-logged operations. See recovery models.
Oracle: archived-redo-based recovery requires the database to run in ARCHIVELOG mode, so that redo is archived.
PostgreSQL: PITR depends on a base backup (for example, one taken with pg_basebackup) and WAL archiving.
MySQL: PITR depends on binary logging being enabled and the binary logs being retained. Binary logging defaults depend on the version and configuration, so verify them explicitly.
Always verify the behavior against the official documentation for your database version, recovery model, and deployment type.
Step 1: Stop the Damage and Preserve Evidence
Stop further writes to the affected database before restoring, and keep the current state and logs.
1. Pause the application or set the database read-only where possible.
2. Take a copy of the current logs and, if feasible, the current data files. The corrupted state may be needed later to extract rows that are still valid.
3. Do not delete or rotate log files, and do not let log retention purge the chain you need.
4. If the cause may be malicious, treat the environment as an incident. CISA's StopRansomware Guide covers response and recovery, and NIST SP 800-184 covers cybersecurity event recovery planning.
Common mistake: restoring over the damaged database immediately. This removes the only copy of data written after the bad change.
Step 2: Choose the Target Time Correctly
Pick the last moment you can prove the data was correct, then restore to slightly before the first bad change.
Ways to locate it:
Application audit tables or change timestamps.
Deployment, job scheduler, and change-ticket logs.
Log inspection with engine tools, such as mysqlbinlog for MySQL, or log reading and marked transactions in SQL Server.
Row-level checks: find the oldest row showing bad values.
The relevant window is detection lag, not the incident time.
Two timelines matter, and they are rarely the same: when the corruption started, and when someone noticed. Three consequences follow from how PITR works:
1. Log retention must exceed your realistic detection lag. If a bad batch job runs on a Friday and is noticed on Tuesday, the base backup and every log from before Friday must still exist. Retention measured only against backup frequency is not enough.
2. The base backup must also predate the first bad change. Logs replay forward from the base. If the only full backup left was taken after the corruption began, no log target can undo it, because the damage is already inside the starting point.
3. The log replays what happened, including the bad change. Stopping before the bad transaction is what removes it. This is why the target is an exact time, position, or marker rather than simply the latest log.
This follows directly from the PITR mechanism described in the PostgreSQL and SQL Server documentation: recovery starts at the base backup and ends at the stop point you define. Practical rule: set retention for both full backups and logs against your maximum plausible detection time, not your backup schedule.
Step 3: Restore Into a Separate Instance First
Direct answer: Restore to a new instance or a differently named database, validate it, then decide how to bring the data back.
An in-place PITR is a trade of good data for good data.
Rolling a production database back to a target time removes every legitimate transaction committed after that time: orders, payments, user changes. Oracle and PostgreSQL documentation explicitly describe engine-specific behaviors after incomplete or point-in-time recovery. Oracle opens the database with RESETLOGS after incomplete recovery, which starts a new incarnation of the database. PostgreSQL begins a new timeline after recovery. Both are described in their official documentation (Oracle, PostgreSQL).
The decision logic that follows:
If the damage is widespread (for example, most tables affected) and the post-target data volume is small, rolling back in place is acceptable.
If the damage is localized (one table or a set of rows) and the system kept running for hours, restore a side copy and copy back only the affected rows or tables.
If you are unsure of the right target time, a side copy lets you try several without risking production.
Managed cloud services commonly follow this pattern. Amazon RDS typically restores point-in-time recoveries to a new DB instance, and Azure SQL Database typically restores to a new database. Always verify the behavior against the official documentation for your database version, recovery model, and deployment type.
Workflow: PITR Commands by Engine
Example only. Run these commands on a separate recovery instance after confirming backup integrity, log continuity, database version, time zone, and the selected recovery target time. Do not execute PITR directly on production without a rollback plan. Always verify syntax, file paths, permissions, recovery models, and versions against your database engine's official documentation.
PostgreSQL
Prerequisites
PostgreSQL 12 or later for the recovery.signal method; check your version's documentation otherwise.
A valid base backup and WAL archiving, with every WAL file from the base backup to the target time available.
A new data directory on a separate recovery instance, and read access to the WAL archive.
Example command
1. Stop the recovery instance and restore the base backup into a new data directory.
2. Set the recovery parameters in postgresql.conf (restore_command, recovery target time and action), then create the signal file:
restore_command = 'cp /mnt/wal_archive/%f "%p"' recovery_target_time = '2026-11-18 09:41:00+00' recovery_target_action = 'promote'
touch /var/lib/postgresql/data/recovery.signal
3. Start the server and watch its log until recovery reaches the target. Recovery targets can also be a transaction ID, LSN, or named restore point.
Validation step: confirm in the server log that recovery reached the target and finished, check that pg_is_in_recovery() returns false after promotion, then query the affected tables.
Common failure point: a missing WAL segment. Recovery halts at the gap, and you can only reach the point before it.
Official reference: PostgreSQL continuous archiving and PITR and recovery target settings.
MySQL
Prerequisites
Binary logging was enabled before the incident, and the binary logs from the backup position onward are available. Defaults depend on version and configuration.
A full backup with its recorded binary log position.
A separate recovery server, and knowledge of how mysqlbinlog interprets time zones in your version.
Example command
1. Restore the full backup to the recovery server.
2. Replay binary logs from the recorded position up to the target:
mysqlbinlog --start-position=<position> --stop-datetime='2026-11-18 09:41:00' binlog.000123 binlog.000124 | mysql -u root -p
3. To skip one bad statement but keep later ones, use position-based stops (--stop-position, then --start-position) instead of a time.
Validation step: confirm the replay finished without errors, then compare row counts and key values in the affected tables with trusted reports.
Common failure point: time zone interpretation of the stop time. Check how your mysqlbinlog version reads datetime values before relying on them.
Official reference: MySQL point-in-time recovery.
SQL Server
Prerequisites
The database uses the full or bulk-logged recovery model with an unbroken log chain. The simple model does not support log backups, and bulk-logged operations can limit point-in-time restore.
A full backup and all later log backups, plus a tail-log backup if the database is still accessible.
Free space for a restored copy under a new database name, and permission to run RESTORE.
Example command
1. Back up the tail of the log if the database is accessible, so the most recent changes are not lost.
2. Restore the full backup with NORECOVERY under a new name, using MOVE for file paths.
3. Restore log backups in order with NORECOVERY, and the final one with STOPAT:
RESTORE DATABASE Sales_Recovered FROM DISK = 'D:\backup\Sales_full.bak' WITH NORECOVERY, MOVE 'Sales' TO 'E:\data\Sales_Recovered.mdf', MOVE 'Sales_log' TO 'F:\log\Sales_Recovered.ldf'; RESTORE LOG Sales_Recovered FROM DISK = 'D:\backup\Sales_log1.trn' WITH NORECOVERY; RESTORE LOG Sales_Recovered FROM DISK = 'D:\backup\Sales_log2.trn' WITH STOPAT = '2026-11-18T09:41:00', RECOVERY;
Validation step: run DBCC CHECKDB on the recovered database and compare key data. According to Microsoft documentation, the recovery point is the latest committed transaction at or before the STOPAT time.
Common failure point: restoring logs out of sequence, or using RECOVERY too early, which blocks further log restores.
Official reference: Microsoft: restore a SQL Server database to a point in time (full recovery model).
Oracle
Prerequisites
ARCHIVELOG mode, with valid archived redo logs from the backup to the target time.
RMAN backups of the database and control file, and a mounted instance on a recovery host.
A plan for the next full backup, because incomplete recovery opens a new database incarnation.
Example command
With the database mounted:
RUN {
SET UNTIL TIME "TO_DATE('2026-11-18 09:41:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;This is incomplete recovery, so RESETLOGS is required. Oracle also offers Flashback Database as an alternative where it is configured.
Validation step: confirm the database opened in read-write mode, check the affected data, and take a fresh backup of the new incarnation.
Common failure point: missing archived redo logs between the backup and the target time, or an unsuitable time format for your NLS settings.
Official reference: Oracle Backup and Recovery documentation. Verify details for your release.
Step 4: Validate Before Switching Over
Confirm integrity and business correctness on the recovered copy before it replaces anything.
1. Run engine integrity checks (for example DBCC CHECKDB on SQL Server).
2. Compare row counts and key aggregates with trusted reports.
3. Spot-check records you know were affected.
4. Have the application owner confirm the data in a test run.
5. Record the final recovery point for the incident report.
Step 5: Bring the Data Back and Close the Gap
Either switch to the recovered instance, or export the repaired rows into production and reconcile post-target changes.
Switch over: repoint applications, then rebuild replicas and take a fresh full backup, because the recovery created a new history (timeline or incarnation).
Merge back: export only the affected tables or rows from the recovered copy and import them. Confirm foreign keys and application logic.
Replay business events: re-enter or re-run transactions that occurred after the target, using application logs or queues.
Decision Matrix: Which Recovery Approach to Use
Situation | Recommended approach | Reason | Trade-off |
Single table dropped, system still running | Restore side copy, copy table back | Preserves all other recent data | Needs spare capacity and a merge step |
Whole database corrupted, little post-target activity | In-place PITR or restore to new server and cut over | Simplest and fastest | Post-target transactions are lost |
Target time uncertain | Several side-copy restores | Can compare candidates | Takes more time and storage |
Cloud managed database | Provider's point-in-time restore to new instance | Built into the service | Limited to the provider's retention window and settings |
Physical storage failure | Restore to healthy hardware | Removes bad storage from the path | May need a new host and longer RTO |
Ransomware | Restore from isolated, verified backups, then PITR | Reduces risk of reintroducing malware | Needs clean environment first |
Best Practices
Schedule frequent log backups. Log backup interval is effectively your achievable RPO.
Keep retention longer than your realistic detection lag.
Store backups and logs on separate, isolated storage. CISA's ransomware guidance recommends offline or isolated, tested backups.
Test restores to a given point in time on a schedule. NIST's contingency planning guide (SP 800-34) treats testing and exercising recovery plans as a core part of planning.
Document the runbook per engine, including time zone assumptions.
Monitor for gaps in the log chain and for backup repository space.
Operationalizing PITR Across Many Databases
What Vinchin offers. Vinchin Backup & Recovery can be used to manage database backups and log backups from one console. According to Vinchin Backup & Recovery documentation, point-in-time restore relies on log backups; without them, a restore returns the database only to the time its full, incremental or differential backup was created. Vinchin describes log-based PITR on its Oracle and SQL Server pages, and its feature overview lists log backup, which captures transaction logs or archived redo logs. The SQL Server page lists restore options: overwrite the original database, create a new database, or restore to another machine. For the concept itself, see What Is Point-in-Time Recovery for Databases.
What to verify first. Actual support depends on the database type, database version, deployment type and Vinchin product version. Check the compatibility lists in the Vinchin knowledge base before planning: SQL Server, PostgreSQL, and Oracle. Vinchin's SQL Server guidance also recommends a separate backup task for databases in simple recovery mode, rather than mixing them with other recovery types in one task.
FAQs
Q1: Can I restore only one table to a point in time?
Most engines do not offer a native single-table PITR from a backup. The usual method is to restore the full database to a side instance at the target time and then export the table. Oracle also supports tablespace-level options in some cases, depending on version and configuration.
Q2: Does PITR protect me if the bad change was committed long ago and discovered months later?
Only if the base backup and the logs from before that change still exist. Otherwise, you need a different repair method, such as reconstructing data from application records.
Q3: Will PITR remove ransomware from the database server?
No. PITR restores data, not the host. Rebuild or clean the server first, then restore, so the same compromise does not recur.
Q4: What is the difference between PITR and a snapshot?
A snapshot captures a single moment. PITR adds the log stream, so you can choose a moment between backups. A snapshot on the same storage as the database also shares that storage's failure risk.
Q5: Do I lose backups taken after the recovery point?
It depends on the engine. After Oracle incomplete recovery, later backups may not be usable for further restores beyond the new incarnation. Take a fresh full backup after any PITR.
Q6: How much does PITR cost?
Costs arise from log storage, retention period, and the spare capacity required for test restores. Longer retention and busier databases produce more log volume.
Q7: How far back can a cloud database be restored?
It depends on the service's configured retention. Check your provider's documentation for current limits.
Conclusion
Point-in-time recovery works when three decisions were made before the incident: logs are captured, retention outlasts detection, and restores are tested. During the incident, the decisions are where to restore and which time to target. Recover to a side copy, validate it, and only then commit.
Share on: