Database Backup — Database Scaling

Database Backup means creating a recoverable copy of database data so it can be restored after accidental deletion, corruption, hardware failure, security incidents, or other disasters.

High availability and replication keep systems available, but backups protect recoverability.

Why Do We Need Backups?

Imagine someone accidentally executes:

DELETE FROM Orders;

A replica may replicate that deletion too.

flowchart LR
    A[Application] --> B[Primary Database]
    B -. Replication .-> C[Secondary]

    D[Accidental DELETE] --> B
    B -. Delete Replicated .-> C

    E[Backup] --> F[Recover Original Data]

This is why replication is not a replacement for backup.

Types of SQL Server Backups

Backup Strategy

A common SQL Server strategy is:

Sunday       → Full Backup
Monday       → Differential + Log Backups
Tuesday      → Differential + Log Backups
Wednesday    → Differential + Log Backups
...

flowchart LR
    A[Full Backup] --> B[Differential Backup]
    B --> C[Transaction Log]
    C --> D[Transaction Log]
    D --> E[Transaction Log]

Complete SQL Server Example

1. Create Database

CREATE DATABASE OrdersDb;
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 Sample Data

INSERT INTO Orders
    (OrderId, CustomerId, Amount, Status, CreatedAt)
VALUES
    (1, 101, 1000.00, 'Completed', '2026-09-25'),
    (2, 102, 2500.00, 'Pending', '2026-09-26'),
    (3, 103, 4500.00, 'Completed', '2026-09-27');
GO

SELECT *
FROM Orders
ORDER BY OrderId;
GO

Expected output:

OrderId  CustomerId  Amount   Status      CreatedAt
-------  ----------  -------  ----------  -------------------
1        101         1000.00  Completed   2026-09-25
2        102         2500.00  Pending     2026-09-26
3        103         4500.00  Completed   2026-09-27

Full Backup

A full backup contains the database required to restore the database to the point represented by that backup.

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

Example output:

10 percent processed.
20 percent processed.
...
100 percent processed.

Processed 3 pages for database 'OrdersDb'.

The exact number of pages depends on the database.

Make Another Change

INSERT INTO Orders
    (OrderId, CustomerId, Amount, Status, CreatedAt)
VALUES
    (4, 104, 6000.00, 'Completed', '2026-09-28');
GO

Differential Backup

A differential backup stores changes made since the most recent full backup.

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

Transaction Log Backup

Transaction log backups require the database to use the FULL or BULK_LOGGED recovery model.

ALTER DATABASE OrdersDb
SET RECOVERY FULL;
GO

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

Backup Chain

flowchart LR
    A[Full Backup] --> B[Differential Backup]
    A --> C[Log Backup 1]
    C --> D[Log Backup 2]
    D --> E[Log Backup 3]

For a restore involving a full backup, differential backup, and subsequent log backups, the backups must belong to the appropriate backup chain.

Verify Backup

SQL Server provides RESTORE VERIFYONLY to check whether a backup can be read and appears valid.

RESTORE VERIFYONLY
FROM DISK = 'C:\SQLBackups\OrdersDb_Full.bak';
GO

Example output:

The backup set on file 1 is valid.

Restore Full Backup

Suppose the database is lost and we need to restore the full backup.

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',
    RECOVERY,
    REPLACE,
    STATS = 10;
GO

The logical file names in a real database should be checked using:

RESTORE FILELISTONLY
FROM DISK = 'C:\SQLBackups\OrdersDb_Full.bak';
GO

Full + Differential Restore

If we have:

Full Backup
     +
Differential Backup

we can restore the full backup first and then the differential backup.

flowchart LR
    A[Full Backup] --> B[Restore Full]
    B --> C[Restore Differential]
    C --> D[Database Recovered]

Full + Differential + Log Restore

For point-in-time recovery, the process can continue with transaction log backups.

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

When applying multiple backups, intermediate restores are normally performed with NORECOVERY so additional backups can be applied.

ASP.NET Core Web API

The Web API normally does not perform database backups itself.

Backup should be handled by the database platform, SQL Server Agent, cloud-managed backup service, or an operational automation system.

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

    B --> C[Backup Strategy]

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

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

The API's responsibility is primarily to handle database errors and continue operating after a database is restored.

Example API

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

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

    [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);
    }

    [HttpPost]
    public async Task<IActionResult> CreateOrder(Order order)
    {
        _db.Orders.Add(order);

        await _db.SaveChangesAsync();

        return CreatedAtAction(
            nameof(GetOrder),
            new { id = order.OrderId },
            order);
    }
}

If the database needs to be restored, the API code generally remains unchanged. The database connection continues pointing to the recovered database endpoint.

Backup Storage

Do not keep the only backup on the same database server.

flowchart LR
    A[Production SQL Server] --> B[Backup Job]
    B --> C[Backup Storage]

    C --> D[Different Server]
    C --> E[Cloud Object Storage]
    C --> F[Offsite / DR Location]

If the database server and its local backup disk fail together, a local-only backup may be useless.

Backup vs Replication vs Failover

A production system often needs all three.

Important Interview Point

Backups protect against logical problems that replication may also copy.

For example:

Developer accidentally deletes data
          ↓
Primary Database
          ↓
Replication
          ↓
Secondary Database
          ↓
Deleted data exists nowhere useful

Backup
          ↓
Historical copy
          ↓
Restore
          ↓
Recover data

Backup Retention

A production backup strategy should define:

• How frequently full backups run.
• How frequently differential backups run.
• How frequently log backups run.
• How long backups are retained.
• Where backups are stored.
• How backups are encrypted.
• How often restores are tested.

Restore Testing

A backup that has never been restored should not automatically be considered a reliable recovery mechanism.

flowchart LR
    A[Backup] --> B[Restore Test]
    B --> C{Successful?}

    C -->|Yes| D[Recovery Ready]
    C -->|No| E[Fix Backup Strategy]
    E --> A

Interview Scenario

Interviewer: Your primary database and secondary replica both contain incorrect data because an application accidentally deleted millions of rows. What would you do?

Answer: I would not rely on the replica because the deletion may already have been replicated. I would use the backup chain to restore the database to an appropriate recovery point. Depending on the required RPO, I could restore a full backup, differential backup, and transaction log backups up to the required point in time.

Common Interview Questions

• Why do we need database backups?
• Full vs differential vs transaction log backup?
• Why is replication not a backup?
• What is a backup chain?
• What is SQL Server recovery model?
• How do you restore a database?
• How do you recover accidentally deleted data?
• Where should backups be stored?
• How do you test backups?
• How are backups related to RPO?

Key Takeaways

• Backup is a recovery mechanism.
• Full backup captures the database baseline.
• Differential backup captures changes since the last full backup.
• Transaction log backups support granular recovery and point-in-time recovery.
• Replication and failover do not replace backups.
• Backups should be stored separately from the production database.
• Restore testing is an important part of a backup strategy.
• Backup frequency and retention should be designed around RPO and business requirements.

Conclusion

Production Database
        ↓
Backup Strategy
        ↓
Full Backup
        ↓
Differential Backup
        ↓
Transaction Log Backups
        ↓
Secure Backup Storage
        ↓
Restore When Required
        ↓
Recovered Database

For system-design interviews, remember: replication helps availability, failover helps continuity, and backups provide recovery from historical or corrupted states.