How to Restore a Database to a Specific Point in Time After Data Corruption

Point-in-time recovery returns a corrupted database to its last good moment using a clean full backup and continuous logs. This guide covers target selection, side-copy restores, engine commands, validation and troubleshooting.

Free Download
for VM, OS, DB, File, NAS, etc.
amelia-luo

Updated by Amelia Luo on 2026/11/19

Table of contents
  • 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

PostgreSQL continuous archiving and PITR                

MySQL

Binary log

MySQL point-in-time recovery                

SQL Server

Transaction log (full or bulk-logged recovery model)

Microsoft: restore to a point in time                

Oracle

Redo log, archived (ARCHIVELOG mode)

Oracle Backup and Recovery documentation                

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.

Download Free TrialFor Multi Hypervisors ↖        
* Free Secure Download

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:

Categories: Database Backup