Understanding Database Backup Corruption
SQL dump files — the most common form of database backup — are plain text files. This is both an advantage and a limitation when it comes to recovery. Because they are text-based, a partial or truncated dump may still contain hundreds of thousands of readable records even if the file cannot be imported cleanly into a database server. However, the import process used by MySQL and PostgreSQL is strict: a single syntax error, an incomplete statement, or a missing transaction boundary will cause the entire import to fail.
The most common corruption scenario for SQL dumps is truncation — the backup process was interrupted before the file was fully written. This typically occurs when a cron job times out, a disk quota is reached, or a hosting environment enforces execution time limits on large mysqldump operations. The result is a file that ends abruptly mid-statement, or one that is missing the final few tables entirely.
Binary database exports — such as those produced by MySQL's physical backup tools, pg_basebackup, or database-specific export formats from hosted platforms — present a different set of challenges. These formats are format-dependent and more sensitive to any form of corruption, but they can sometimes be partially salvaged when damage is localised.
Typical Corruption Scenarios
- Truncated SQL dumps: The file ends partway through an INSERT or COPY statement, leaving a large but unimportable file. Recovery involves parsing the intact records from before the cut point.
- Missing character set declarations: Some hosting panel exports omit the SET NAMES declaration, causing encoding errors during import. This is a configuration issue rather than corruption, and it is addressable.
- Compressed SQL with archive damage: SQL dumps are often stored inside GZ or ZIP containers. If the container is damaged, the approach combines archive repair with SQL analysis.
- Table-level corruption within a dump: In some cases, specific tables within an otherwise intact dump contain malformed data — for example, from a plugin that serialised PHP objects incorrectly before dumping. We attempt to recover the surrounding tables while flagging the problematic entries.
- Corrupted binary backups: Physical MySQL backup files (.ibd, .frm) or PostgreSQL base backups that have been stored improperly can sometimes be accessed with appropriate recovery tools if the InnoDB data dictionary is still readable.
Diagnostic Process for Database Backups
We begin database backup analysis by determining the file format: plain SQL text, compressed SQL, binary export, or a proprietary hosting panel format. Once identified, we assess the file's structure against the expected format specification.
For SQL dumps, we scan the file from the beginning to identify the mysqldump or pg_dump header, the database and table structure declarations, and the data sections. We map which tables are fully present, which are partially present, and which are absent entirely. We then calculate what proportion of the dataset is recoverable in a clean, importable form.
For binary backups, the diagnostic approach is format-specific. MySQL InnoDB backups, for example, can sometimes be inspected by parsing the tablespace files directly if the .frm metadata files are available. PostgreSQL base backups require the pg_filenode mapping and the global tablespace to be intact. We will advise at the diagnostic stage if a binary backup format is outside the scope of what we can address.
Recovery and Reconstruction
For truncated SQL files, the primary recovery method is to extract all complete INSERT statements or COPY blocks from the readable portion of the file, reassemble them into a clean importable format, and verify that the result can be imported without errors. Where a transaction spans the truncation point, we identify the last complete row and exclude everything from that point forward to avoid partial record insertion.
For files where individual tables are malformed, we attempt table-level extraction — producing a separate SQL file for each recoverable table. This allows the recovered data to be imported selectively, leaving out only the affected table or tables.
We do not attempt to invent or reconstruct data that is not present in the file. Where records are simply absent because they were never written to the dump, that data cannot be recovered through file analysis. Recovery is limited to what the file actually contains.
Deliverables
Upon completion, you will receive the recovered SQL file or files, a log of tables recovered and tables not recoverable, and a written report describing the file's condition, the damage found, and how recovery was carried out. If we identify the likely cause of the corruption, this will also be documented.
We recommend importing recovered SQL files into a staging environment first, verifying the content, and then proceeding to production if the result is acceptable. We can provide guidance on the import process if required.