Private-source disclosure: This case study is based on a private repository. All examples are intentionally sanitized. No credentials, customer data, internal URLs, infrastructure identifiers or proprietary deployment configuration are reproduced.

SQLite looks like a file and encourages the wrong mental model

SQLite is a file. You can cp it. You can scp it. You can attach it to an email if you're feeling reckless.

This is SQLite's greatest strength and its most dangerous illusion.

A successful file copy does not equal a trustworthy backup. Successfully creating a second file proves that bytes moved from A to B. It does not prove the artifact represents database state with the guarantees your application requires.

I build an offline desktop hotel management system. The database holds reservations, guest records, financial transactions, and occupancy state. If a workstation fails and a restore is needed, the backup has to be usable.

A successful copy does not equal a trustworthy backup

Three problems with File.Copy(database.db, backup.db):

Concurrent writes. SQLite in WAL mode allows one writer and multiple readers. If you copy the .db file while a transaction is open, you might capture:

  • A committed transaction in the WAL that hasn't checkpointed yet (missing from the main file)
  • A partially-written page
  • A state where the file and its WAL are inconsistent

Your copy "succeeds." The resulting file might pass an integrity check. But it doesn't represent a coherent database state.

No verification. A successful copy tells you the filesystem operation completed. It doesn't tell you:

  • Whether the resulting artifact is a valid SQLite database
  • Whether pages are corrupt
  • Whether the artifact matches what you intended to back up

No artifact integrity. Someone can modify the backup file after creation—accidentally, maliciously, or through bitrot. Without a cryptographic checksum captured at creation, you cannot detect tampering or corruption between backup and restore.

Copying bytes is easy. Trusting the artifact requires verification.

How the actual backup pipeline works

My backup flow treats backup creation as a verified state transition:

var stagedDatabasePath = Path.Combine(databaseStagingDirectory, ...);
await CreateDatabaseSnapshotAsync(sourceDatabasePath, stagedDatabasePath, ...);
await ValidateDatabaseIntegrityAsync(stagedDatabasePath, ...);

I use SQLite's BackupDatabase() API, not file operations:

await using var sourceConnection = new SqliteConnection(sourceConnectionString);
await sourceConnection.OpenAsync(...);

await using var destinationConnection = new SqliteConnection(destinationConnectionString);
await destinationConnection.OpenAsync(...);

sourceConnection.BackupDatabase(destinationConnection);

BackupDatabase() creates a page-level consistent snapshot. Uncommitted transactions are excluded. The resulting file is a coherent database state even if the source had an open WAL.

I normalize the snapshot immediately:

await using var checkpointCommand = destinationConnection.CreateCommand();
checkpointCommand.CommandText = "PRAGMA wal_checkpoint(TRUNCATE);";
await checkpointCommand.ExecuteNonQueryAsync(...);

await using var journalModeCommand = destinationConnection.CreateCommand();
journalModeCommand.CommandText = "PRAGMA journal_mode=DELETE;";
await journalModeCommand.ExecuteScalarAsync(...);

This checkpoints any pending WAL entries and switches to DELETE mode, producing a single self-contained file with no sidecar dependencies.

The snapshot stages in a temporary directory before archiving:

.staging-{guid}/
  database/
    hotelpms.db
  settings/
    appsettings.json

Only after validation does the staged snapshot enter the ZIP archive.

Verification before success

I validate database integrity before declaring backup success:

await using var connection = new SqliteConnection(...);
await connection.OpenAsync(...);

await using var command = connection.CreateCommand();
command.CommandText = "PRAGMA integrity_check;";
var result = (string?)await command.ExecuteScalarAsync(...);

if (!string.Equals(result, "ok", StringComparison.OrdinalIgnoreCase))
{
    throw new InvalidOperationException("Database integrity check failed.");
}

PRAGMA integrity_check performs a low-level structural consistency check of the SQLite database. It detects problems such as:

  • Malformed records
  • Missing pages or pages used more than once
  • Index inconsistencies and missing index entries
  • Table and index entries out of sequence
  • Freelist integrity issues
  • UNIQUE, CHECK, and NOT NULL constraint errors

It does not check foreign-key violations—those require PRAGMA foreign_key_check, which I do not currently perform. It does not prove business correctness. A database that passes integrity_check can still hold inconsistent application state or violated foreign-key constraints. But it proves the SQLite file is readable and structurally sound at the storage layer.

A failed integrity check aborts backup creation. I never create an artifact I cannot validate.

Database integrity vs artifact integrity

These are separate concerns:

Database integrity answers: "Is this a valid SQLite database?"

  • Validated once, immediately after snapshot creation
  • Tests the extracted database file
  • Proves the snapshot operation succeeded

Artifact integrity answers: "Is this the same ZIP I created?"

  • Validated every time before restore
  • Tests the backup archive
  • Proves the artifact wasn't tampered with or corrupted

I compute SHA-256 over the final ZIP:

await using var stream = new FileStream(destinationPath, ...);
using var sha256 = SHA256.Create();
var hash = await sha256.ComputeHashAsync(stream, ...);
return Convert.ToHexString(hash);

The checksum is stored in my backup catalog and returned with the artifact. Before restore, I verify:

var actualArchiveChecksum = await CalculateChecksumAsync(archivePath, ...);
if (!string.Equals(actualArchiveChecksum, expectedArchiveChecksum, StringComparison.OrdinalIgnoreCase))
{
    throw new InvalidOperationException("Archive verification failed.");
}

A checksum mismatch rejects restore before extraction. I never apply an artifact whose integrity I cannot confirm.

Neither check proves business correctness. An intact, valid database can still represent the wrong day's transactions if backup scheduling failed. But both checks reduce the surface area where silent corruption can destroy data.

Why restore is the dangerous half

A failed backup leaves you with a useless backup file. Inconvenient, but your live database is untouched.

A bad restore can destroy the currently working state.

This asymmetry justifies more conservative restore behavior:

  1. Validate incoming state before replacement
  2. Preserve rollback path before destructive change
  3. Replace atomically where possible
  4. Test rollback on failure

I treat restore as a controlled state transition, not a file overwrite.

Validate incoming state before replacement

I extract and validate the candidate database before touching the live system:

await Task.Run(() => ZipFile.ExtractToDirectory(archivePath, extractDirectory, ...));

var extractedDatabasePath = ResolveExtractedFilePath(extractDirectory, "database", ...);
stagedDatabasePath = CreateTemporarySiblingFilePath(targetDatabasePath, "restore");
await CopyFileAsync(extractedDatabasePath, stagedDatabasePath, ...);
await ValidateDatabaseIntegrityAsync(stagedDatabasePath, ...);

The extracted database runs through the same integrity check I applied at backup creation. If it fails, I abort before the live database is touched.

Validation happens in a staging path, not in place. The live database remains accessible while I verify the candidate.

Create a rollback path first

Before replacing the live database, I snapshot the current state:

databaseRollbackPath = databaseExisted
    ? CreateTemporarySiblingFilePath(targetDatabasePath, "rollback")
    : string.Empty;

if (databaseExisted)
{
    await CreateDatabaseSnapshotAsync(targetDatabasePath, databaseRollbackPath, ...);
}

I use the same BackupDatabase() API I use for backup creation. The rollback snapshot is a consistent database, not a blind file copy.

If restore fails after replacement starts, I have a known path back:

catch
{
    await RollbackDatabaseAsync(
        targetDatabasePath,
        databaseRollbackPath,
        databaseExisted,
        databaseReplaced,
        ...);
    throw;
}

The rollback restores the pre-restore database state using the same snapshot mechanism. I don't attempt to "undo" a partial replacement—I restore from a known good state captured before the operation began.

Configuration files follow the same pattern:

settingsRollbackPath = settingsExisted
    ? CreateTemporarySiblingFilePath(targetSettingsPath, "rollback")
    : string.Empty;

ReplaceTargetFileWithRetries(stagedSettingsPath, targetSettingsPath, settingsRollbackPath);

On Windows, same-volume File.Replace provides atomic replacement semantics that are useful here, but the implementation still treats replacement as a failure boundary and maintains rollback handling.

Protect customer data from the updater

My desktop updater separates ownership boundaries explicitly:

private bool IsProtectedFile(string relativePath) =>
    relativePath.Equals("appsettings.json", StringComparison.OrdinalIgnoreCase) ||
    relativePath.Contains("Data\\", StringComparison.OrdinalIgnoreCase) ||
    relativePath.Contains("Logs\\", StringComparison.OrdinalIgnoreCase);

Application binaries are replaceable. Customer databases, local configuration, and operational logs are not.

The updater's inspected update path filters protected locations before its replacement step:

foreach (var sourceFile in filesToUpdate)
{
    var relativePath = Path.GetRelativePath(_updateWorkDirectory, sourceFile);

    if (IsProtectedFile(relativePath))
    {
        _logger.LogInformation("Protected file skipped: {FileName}", relativePath);
        continue;
    }

    // Backup and replace
}

This design prevents updater logic from touching customer data paths.

The backup/restore system operates independently. Restore explicitly targets the database and settings because that is its defined responsibility. The updater never touches those paths because they are outside its responsibility.

Clear ownership boundaries prevent scope creep from becoming a data loss incident.

What this architecture does NOT guarantee

This is not a silver bullet. Real limitations:

Business correctness. A validated, intact backup can still represent corrupted application state if a bug wrote bad data before backup ran.

Filesystem-level corruption. Integrity checks detect structural inconsistencies at SQLite's storage layer, not block-level filesystem damage that hasn't surfaced as database corruption yet.

Rollback snapshot creation failure. If rollback snapshot creation fails, restore must abort before replacement. An incomplete rollback artifact cannot be treated as a recovery point.

Power loss during replacement. The rollback mechanism depends on completing the rollback snapshot before destructive replacement starts. A power loss after replacement begins can produce states not covered by ordinary exception handling. The application-level rollback flow is exercised through normal exception paths, but crash consistency across sudden process termination or OS failure is not proven.

Concurrent restore attempts. I retry on transient locks, but simultaneous restore attempts from multiple processes can still interfere.

Disk space exhaustion. If extraction or snapshot creation runs out of space mid-operation, rollback might fail because the rollback file is incomplete. No pre-flight disk space check prevents this.

I reduce the failure surface. I don't eliminate it.

What I would repeat or change

Keep:

  • BackupDatabase() API over file copy—consistent snapshots are worth the API surface
  • Integrity validation before artifact creation—catching corruption at backup time is cheaper than discovering it at restore time
  • Rollback snapshot before replacement—the implementation creates a rollback path before destructive operations
  • Protected file boundaries in updater—explicit filtering prevents updater logic from touching customer data paths

Change:

  • Test restore validation more aggressively. I validate extracted databases but don't verify they're openable by EF Core until after replacement. A database that passes integrity_check but fails EF Core's migration version check will break on replacement.
  • Add pre-restore simulation. Open the extracted database in a throwaway DbContext, run a basic query, confirm the schema version. Reject restore before replacement if this fails.
  • Improve rollback test coverage. I test the happy path and checksum rejection. I don't systematically test rollback after partial replacement, filesystem-full during rollback, or rollback when the target was deleted mid-restore.
  • Separate staging and production restore paths in tests. Production restore runs against live state. My test suite creates temporary databases. Some failure modes only surface under concurrent access, and my tests miss them.

Engineering takeaway

The lesson is not "use SQLite's backup API" or "calculate checksums."

The lesson is: treat backup and restore as verified state transitions, not file operations.

Ask these questions:

  1. Backup: Can I prove the artifact represents a consistent state?
  2. Backup: Can I prove the artifact wasn't tampered with between creation and use?
  3. Restore: Can I validate incoming state before replacing live state?
  4. Restore: If replacement fails, do I have a known path back?
  5. Updater: Are ownership boundaries clear enough that a bug in one system cannot destroy data owned by another?

The answers are implementation-specific. For SQLite, BackupDatabase() answers question 1. For archives, SHA-256 answers question 2. For restore, staging + rollback snapshots answer questions 3 and 4. For updater, explicit protected-path filtering answers question 5.

But the questions apply regardless of stack. A Postgres backup needs the same guarantees. The mechanisms differ—pg_dump, transaction IDs, archive checksums—but the principle is identical.

Copying bytes is easy. Building a backup artifact you can trust—and a restore process that doesn't casually destroy the currently working state—requires treating backup and restore as verified state transitions.

This case study describes implementation from a private desktop hotel management system codebase. It is not exhaustively tested against every filesystem, concurrency or crash-recovery edge case. Use the principles, not the snippets, as guidance for your own system.