Point-in-Time Recovery — Database Scaling

Point-in-Time Recovery (PITR) means restoring a database to a specific moment in time, usually just before an accidental deletion, corruption, or other unwanted change.

Point-in-Time Recovery (PITR) means restoring a database to a specific moment in time, usually just before an accidental deletion, corruption, or other unwanted change.

Why Do We Need PITR?

Suppose someone accidentally deletes important orders at 10:35 AM.

10:00 AM → Database healthy
10:20 AM → New orders
10:35 AM → Accidental DELETE
10:40 AM → Problem discovered

We don't necessarily want to restore the database to yesterday's full backup. We want to recover it to approximately 10:34:59 AM.

flowchart LR
    A[Full Backup] --> B[Transaction Log 1]
    B --> C[Transaction Log 2]
    C --> D[Transaction Log 3]
    D --> E[Accidental DELETE]
    E --> F[Recovery Point]

How PITR Works

SQL Server typically uses:

Full backup
Differential backup
Transaction log backups

The log backups allow SQL Server to replay transactions up to the required recovery point.

flowchart TD
    A[Full Backup] --> B[Differential Backup]
    B --> C[Log Backup 1]
    C --> D[Log Backup 2]
    D --> E[Log Backup 3]
    E --> F[Target Time]
    F --> G[Recovered Database]

Complete SQL Server Example

1. Create Database

CREATE DATABASE OrdersDb;
GO

ALTER DATABASE OrdersDb
SET RECOVERY FULL;
GO

USE OrdersDb;
GO

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    Status VARCHAR(30) NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);
GO

2. Insert Initial Data

INSERT INTO Orders
    (OrderId, CustomerId, Amount, Status, CreatedAt)
VALUES
    (1, 101, 1000.00, 'Completed', '2026-09-27 10:00:00'),
    (2, 102, 2500.00, 'Completed', '2026-09-27 10:10:00');
GO

SELECT *
FROM Orders
ORDER BY OrderId;
GO

Expected output:

OrderId  CustomerId  Amount   Status      CreatedAt
-------  ----------  -------  ----------  -------------------
1        101         1000.00  Completed   2026-09-27 10:00:00
2        102         2500.00  Completed   2026-09-27 10:10:00

3. Take a Full Backup

BACKUP DATABASE OrdersDb
TO DISK = 'C:\SQLBackups\OrdersDb_Full.bak'
WITH
    INIT,
    COMPRESSION,
    STATS = 10;
GO

4. Add More Data

INSERT INTO Orders
    (OrderId, CustomerId, Amount, Status, CreatedAt)
VALUES
    (3, 103, 3500.00, 'Completed', '2026-09-27 10:20:00'),
    (4, 104, 4500.00, 'Pending', '2026-09-27 10:25:00');
GO

5. Take Transaction Log Backup

BACKUP LOG OrdersDb
TO DISK = 'C:\SQLBackups\OrdersDb_Log_1.trn'
WITH
    INIT,
    COMPRESSION,
    STATS = 10;
GO

6. Accidental DELETE

Suppose an application accidentally executes:

DELETE FROM Orders
WHERE OrderId IN (3, 4);
GO

Now the data is gone.

SELECT *
FROM Orders
ORDER BY OrderId;
GO

Current output:

OrderId  CustomerId  Amount   Status      CreatedAt
-------  ----------  -------  ----------  -------------------
1        101         1000.00  Completed   2026-09-27 10:00:00
2        102         2500.00  Completed   2026-09-27 10:10:00

7. Take Another Log Backup

The transaction log backup after the incident captures the subsequent log records needed for recovery.

BACKUP LOG OrdersDb
TO DISK = 'C:\SQLBackups\OrdersDb_Log_2.trn'
WITH
    INIT,
    COMPRESSION,
    STATS = 10;
GO

8. Restore to a Point Before the DELETE

Assume the accidental DELETE occurred at:

2026-09-27 10:30:00

We want to recover to:

2026-09-27 10:29:59

First restore the full backup with NORECOVERY:

RESTORE DATABASE OrdersDb
FROM DISK = 'C:\SQLBackups\OrdersDb_Full.bak'
WITH
    MOVE 'OrdersDb'
        TO 'C:\SQLData\OrdersDb.mdf',
    MOVE 'OrdersDb_log'
        TO 'C:\SQLData\OrdersDb_log.ldf',
    NORECOVERY,
    REPLACE,
    STATS = 10;
GO

Then apply the required log backups.

RESTORE LOG OrdersDb
FROM DISK = 'C:\SQLBackups\OrdersDb_Log_1.trn'
WITH
    NORECOVERY,
    STATS = 10;
GO

Finally, restore the next log backup and stop at the required point in time.

RESTORE LOG OrdersDb
FROM DISK = 'C:\SQLBackups\OrdersDb_Log_2.trn'
WITH
    STOPAT = '2026-09-27T10:29:59',
    RECOVERY,
    STATS = 10;
GO

The exact restore sequence and timestamps depend on the actual backup chain and transaction-log contents.

Verify the Recovered Data

SELECT *
FROM Orders
ORDER BY OrderId;
GO

Expected result:

OrderId  CustomerId  Amount   Status      CreatedAt
-------  ----------  -------  ----------  -------------------
1        101         1000.00  Completed   2026-09-27 10:00:00
2        102         2500.00  Completed   2026-09-27 10:10:00
3        103         3500.00  Completed   2026-09-27 10:20:00
4        104         4500.00  Pending     2026-09-27 10:25:00

The unwanted DELETE is not present because recovery stopped before it occurred.

Restore Timeline

flowchart LR
    A[10:00 Full Backup] --> B[10:20 Orders]
    B --> C[10:25 Orders]
    C --> D[10:30 Accidental DELETE]
    D --> E[10:40 Problem Discovered]

    F[Restore Full] --> G[Restore Log 1]
    G --> H[Restore Log 2]
    H --> I[STOPAT 10:29:59]
    I --> J[Recovered Database]

ASP.NET Core Web API

The Web API does not normally perform PITR. Database recovery is an infrastructure/database operation.

However, the API should be designed so that restoring the database does not require application-code changes.

Connection Configuration

{
  "ConnectionStrings": {
    "OrdersDb":
      "Server=sql-listener;Database=OrdersDb;Integrated Security=True;TrustServerCertificate=True;"
  }
}

DbContext

public class OrdersDbContext : DbContext
{
    public OrdersDbContext(
        DbContextOptions<OrdersDbContext> options)
        : base(options)
    {
    }

    public DbSet<Order> Orders => Set<Order>();
}

API Controller

[ApiController]
[Route("api/orders")]
public class OrdersController : ControllerBase
{
    private readonly OrdersDbContext _db;

    public OrdersController(OrdersDbContext db)
    {
        _db = db;
    }

    [HttpGet]
    public async Task<IActionResult> GetOrders()
    {
        var orders = await _db.Orders
            .AsNoTracking()
            .OrderBy(x => x.OrderId)
            .ToListAsync();

        return Ok(orders);
    }

    [HttpGet("{id:int}")]
    public async Task<IActionResult> GetOrder(int id)
    {
        var order = await _db.Orders
            .AsNoTracking()
            .FirstOrDefaultAsync(x => x.OrderId == id);

        if (order == null)
            return NotFound();

        return Ok(order);
    }
}

After the database is restored, the same API can query the recovered state.

PITR Architecture

flowchart TD
    A[ASP.NET Core API] --> B[SQL Server]

    B --> C[Full Backup]
    B --> D[Differential Backup]
    B --> E[Transaction Log Backups]

    C --> F[Backup Storage]
    D --> F
    E --> F

    F --> G[Restore]
    G --> H[Target Point in Time]
    H --> I[Recovered Database]

PITR vs Full Restore

Full Restore	Point-in-Time Recovery
Restores a backup state	Restores to a selected time
May lose changes after backup	Can recover much closer to the failure
Simpler	Requires appropriate log backups

PITR and RPO

Point-in-Time Recovery is closely related to Recovery Point Objective (RPO).

If transaction log backups are taken frequently, the potential data-loss window can be smaller.

flowchart LR
    A[Frequent Log Backups] --> B[Smaller Recovery Gap]
    C[Infrequent Log Backups] --> D[Larger Recovery Gap]

The actual achievable RPO depends on the complete backup and recovery architecture.

Important Interview Point

PITR is different from failover.

Failover tries to keep the service running by switching to another healthy database.

PITR restores historical database state after an unwanted change or disaster.

Failover
Primary Failed
      ↓
Secondary
      ↓
Continue Service


PITR
Bad Data / DELETE
      ↓
Backup Chain
      ↓
Restore
      ↓
Historical State

Interview Scenario

Interviewer: A developer accidentally deletes 10 million records at 2:30 PM. The replica also contains the deleted records. What would you do?

Answer: I would not fail over to the replica because the bad change may already have been replicated. I would use the backup chain to perform point-in-time recovery to a time immediately before the accidental deletion. I would validate the recovered data and then decide how to merge or restore the required records into production safely.

Common Interview Questions

What is Point-in-Time Recovery?
Why do we need PITR?
How does SQL Server perform PITR?
What is STOPAT?
Why are transaction log backups required?
What is NORECOVERY?
What is the difference between backup restore and PITR?
PITR vs failover?
How is PITR related to RPO?
Can replication replace PITR?

Key Takeaways

PITR restores a database to a specific point in time.
SQL Server PITR relies on an appropriate full backup and transaction-log backup chain.
STOPAT can be used to specify the target recovery time.
Replication may replicate accidental changes, so it is not a substitute for PITR.
ASP.NET Core normally does not perform PITR; database infrastructure handles it.
Frequent log backups can reduce the recovery-point gap.
PITR is an important component of a disaster-recovery strategy.

Conclusion

Full Backup
     ↓
Differential Backup
     ↓
Transaction Logs
     ↓
Incident
     ↓
Choose Recovery Time
     ↓
STOPAT
     ↓
Recovered Database
     ↓
ASP.NET Core API

For interviews, remember: PITR answers the question, “How do I recover the database to the state it had just before the problem happened?”

Articles categories

Projects categories

Cheatsheets categories

Videos categories

eBooks categories

Latest articles