Database Archiving vs Backup

Two questions that need different answers

Your application queries the last few months of events, but the organization retains years of history. You also take database backups. It is tempting to remove old events and assume that a backup will cover any future request for them. The missing question is whether you can find and retrieve those records after the backup’s retention window expires.

A backup asks: can I recover the database after failure? An archive asks: does this historical record need to remain in production, and how will I access it elsewhere? The words describe purpose and workflow, not just a particular file extension. A database dump can participate in either process, but it needs the corresponding retention and acceptance checks.

Compare the operational contracts

Define each contract before choosing tools. A recovery process must recreate usable database state at an acceptable recovery point and within an acceptable time. A historical archive needs a documented selection, retained values, a catalog and a retrieval path across its own lifetime.

The table captures common intent, not a restriction on all implementations. Some organizations use backups to retrieve historical data, but that still requires a viable restore environment, retained recovery chains and enough time to locate the requested records.

Why a rolling backup is not automatically a historical archive

Consider an illustrative policy that retains events for several years but keeps recovery backups for a much shorter period. After a planned deletion, new backups will reflect the deleted state. Once older backups expire, they may no longer contain the history you intended to retain. The exact outcome depends on your backup and recovery chain; the point is that its lifetime must match the promised retrieval obligation.

Keeping every old backup indefinitely can preserve data, but it creates a different retrieval problem. Finding one customer’s historical event may require restoring a large database into a compatible isolated environment. You also need software, schema context, keys and permission to read it. Retention on paper is not proof of usable access.

Turn retention into an explicit record policy

Document the relevant datasets, the business timestamp used for eligibility, the required historical lifetime and the exceptions. Decide how corrections, active references and holds affect archival. A date-based selection without those decisions can move records the application still needs or omit records the organization expects to keep.

Then define both ends of the lifecycle. If data may leave production after a period, specify what evidence permits that move. If the retained copy eventually expires, coordinate file removal, catalog changes and any dependent retrieval processes. Treat these as requirements to agree with the responsible teams, not a universal schedule supplied by a tool.

Design a usable archive acceptance test

For an exported batch, verify file existence, readability, identifiers, schema mapping and required value fidelity. Save the selection bounds and mapping version. A row count alone does not prove that the correct records were retained. An object-store checksum does not demonstrate that dates or decimals kept their intended meaning.

Write a retrieval exercise with an actual question: locate a dataset and period, fetch a known retained record, and check the important fields through the intended reader. If restoration to a database is required, test that separately with identifier conflicts and schema changes. Indexes, triggers and application DDL may not be present in a file archive.

• Designing the database-to-Parquet representation →
• Querying retained Parquet with DuckDB →

Keep operational recovery tests separate

A historical archive of selected rows generally lacks the current operational state needed after a failure. It does not automatically restore recent changes, application relationships or the database’s complete structure. Continue the backup and recovery process even when archived data has passed its acceptance tests.

Conversely, a successfully restored backup does not prove that the archive catalog points to complete exports or that a historical query will work next year. Test recovery and historical retrieval independently. Preserve the credentials and encryption-key access required by each process, and confirm that an access restriction or expired key will not make retained data unreadable.

What to decide before reclaiming production capacity

Make a short acceptance record: the selected history and its retention policy; the durable archive and mapping evidence; a successful retrieval exercise; an independent recovery backup; and a cleanup decision scoped to verified, unchanged source records. If one of these is unresolved, a successful export is still only an export.

ColdCTL implements PostgreSQL, MySQL 8.4 and MongoDB 8.0 archival through separate connectors, with local Parquet, S3 and Azure Blob destinations, verification and explicitly authorized source deletion. Its customer-hosted Archive Gateway supports browser retrieval and bounded downloads. Engine-specific limits and production qualification still apply. Its lifecycle is intended to complement recovery backups, not replace them.

• The current ColdCTL lifecycle →
• Self-hosted data archiving and ownership →