Quick answer: On a corrupt database the first task is not to force the engine with recovery flags, but to copy the raw files (MDF, NDF and LDF for SQL Server, ibdata1 and .ibd for MySQL InnoDB, the base directory and WAL for PostgreSQL) bit for bit to another disk. All attempts run on the copy. All three engines store data in fixed size pages (8 KB for SQL Server and PostgreSQL, 16 KB for InnoDB) with a checksum on each page, so corruption is usually limited to a few pages. The recovery order is: first try to reach a consistent state from the transaction log, then try the engine specific recovery mode (SQL Server emergency and DBCC, innodb_force_recovery, PostgreSQL read only startup), and as a last resort, if the engine will not start at all, carve records directly out of the pages in the file. pg_resetwal, high innodb_force_recovery levels and DBCC ... REPAIR_ALLOW_DATA_LOSS can lose data, so they come last.

Database recovery is a different discipline from file recovery. Bringing back a photo means finding the bits; bringing back a database means reestablishing the relationship between pages, transaction logs and consistency rules. This guide covers, at an expert level, how corruption arises for SQL Server, MySQL InnoDB and PostgreSQL, the internal structure of the engines, and which recovery step is safe versus a last resort.

Copy the raw files first, do not touch the engine

On all three engines the most destructive mistake is to run recovery commands while the corrupt database files are still in place. DBCC CHECKDB ... REPAIR_ALLOW_DATA_LOSS, a high innodb_force_recovery level and pg_resetwal change the contents of the files, and if they fail, recovery becomes harder.

The correct order: stop the service, copy the database files at the operating system level to another disk, and run all recovery on those copies. If there is a storage failure (the disk returns bad sectors, the RAID collapsed) take a block level image first; in that case follow the method in our imaging a failing disk article and, if RAID is underneath, our RAID 5 and 6 rebuild article. If ransomware encrypted the files, you need incident response, not database recovery, and encrypted pages cannot be brought back without the key.

The page layout of the three engines

All three engines store data in fixed size pages. Corruption usually affects specific pages, not the whole database; checksums allow the corrupt page to be detected.

Engine Data file Page size Transaction log Recovery mode
SQL Server .mdf, .ndf 8 KB .ldf emergency mode, DBCC CHECKDB
MySQL InnoDB ibdata1, .ibd 16 KB ib_logfile, redo log innodb_force_recovery 1-6
PostgreSQL base/ 8 KB pg_wal read only startup, zero_damaged_pages

Each page has a header, a data body and a checksum. When the engine reads a page it verifies the checksum; if it does not match it treats the page as corrupt and usually marks the whole database suspect. The essence of recovery is to save the healthy pages and repair or skip the corrupt ones.

SQL Server: a suspect database and DBCC

If a SQL Server database finds an inconsistency at startup it goes into SUSPECT and closes to access. Typical causes: a transaction log left half done on sudden power loss, a storage error or a missing LDF file.

The safe order is gradual. First put the database into emergency mode and try read only access; this gives a chance to move the data out without any repair.

ALTER DATABASE [db] SET EMERGENCY;
ALTER DATABASE [db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ([db]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

DBCC CHECKDB reports inconsistencies and states the minimum repair level it suggests. The critical distinction here is this: REPAIR_REBUILD repairs without data loss but is not enough for every kind of corruption; REPAIR_ALLOW_DATA_LOSS achieves consistency by deleting corrupt pages, which means data loss. The latter is run only when there is no backup, as a last resort, and always on a copy.

If the LDF file is lost or the MDF is orphaned from another server, you use attach techniques with the MDF alone; a corrupt boot page (page 9) needs page repair with expert tools. To inspect individual pages you use DBCC PAGE. We covered a similar enterprise server scenario in our Exchange and Active Directory server recovery article.

MySQL InnoDB: the innodb_force_recovery levels

When InnoDB sees an inconsistent page it usually crashes MySQL, and on restart it crashes again on the same page. The solution is the innodb_force_recovery setting, which opens recovery in stages. Starting from the lowest possible level is essential.

Level What it does Risk
1 ignores corrupt pages and continues low
2 background purge does not run low
3 transaction rollback is not performed medium
4 skips corrupt secondary indexes data loss possible
5 ignores the redo log data loss possible
6 redo fully skipped, most aggressive high data loss

The right approach: start with 1, and if MySQL comes up, immediately export all data with mysqldump, then restore to a clean server. If it does not come up, move one level higher. Levels 4 and above mean data loss, so they are used only on a copy and when there is no other way. If it will not open even at level 6, or the tablespace file is physically damaged, you move to expert extraction that reads pages directly from the .ibd file.

An important detail is the file per table setting: each .ibd file holds the 16 KB pages of a single table and the data dictionary lives in ibdata1. When the dictionary is corrupt, carving records from the .ibd pages requires knowing the table schema. This scenario is common in MySQL based e commerce systems such as Magento and WooCommerce; we described the approach in our e commerce data recovery article.

PostgreSQL: WAL, the control file and corrupt pages

PostgreSQL keeps each relation (table, index) as files in the base directory and writes changes first to the WAL (write ahead log). After a crash, a normal startup replays the WAL to reach consistency. The problem begins when the WAL or control file is corrupt.

The most dangerous tool is pg_resetwal. It resets the WAL and can open the server, but it permanently loses transactions that were not replayed and can leave the database logically inconsistent. This is used only when there is no other way, on a copy, and to export the data immediately with pg_dump.

If a specific page is corrupt and returns a read error, enabling zero_damaged_pages per session can zero the corrupt pages and read the rest; this loses the affected rows but recovers the rest of the table. Logical corruption such as transaction id wraparound needs a different intervention. In every case the goal is to move the data out with a consistent dump as soon as the server opens, even read only.

Last resort: carving pages directly from the file

If the engine will not open in any mode, or the file is so damaged it cannot be ingested, recovery becomes independent of the database engine. At this point the expert opens the data file raw, finds the page boundaries from the page headers, extracts the healthy pages whose checksums match, and parses the rows (records) according to the known schema. This is the database adapted form of file carving from a disk; we described the general carving logic in our raw disk recovery and sector carving article. The result is not a consistent live database but an export of the recovered tables; in most incidents that is what the business actually needs.

After recovery: why it should not happen again

After a database recovery the real question is how to keep it from happening again. In most incidents the root cause is a gap in the backup strategy: either no backup was taken, or the backups were on the same corrupt storage, or restore was never tested. Regular, off site backups, a restore drill and defined recovery targets (RPO, RTO) are essential; we covered this in our business continuity and disaster recovery article and our restoring from enterprise backups article.

The DSET approach

At DSET we run database recoveries in our lab at Ankara Hacettepe Teknokent on a copy the raw files first principle. Without touching the production server, we work on copies of the MDF, ibdata and base files, start recovery flags from the lowest data loss risk, and move the data out with a consistent dump as soon as the engine opens. If there is storage or RAID related corruption, we take a block level image first. If a database is stuck in suspect, InnoDB is crashing, or PostgreSQL will not start, contact DSET before running any repair command against the production files. If the incident has a legal dimension, we preserve the chain of custody with the principles in our digital forensics process article.

Frequently asked questions

What level should I start innodb_force_recovery at? Always start at 1. If MySQL comes up at 1 or 2, the data loss risk is low and you should export everything immediately with mysqldump. If it does not come up, raise it one at a time. Because 4, 5 and 6 can cause data loss, they are used only on a copy and when there is no other option; after opening at these levels, the right move is not to repair and keep using the database but to export it and restore to a clean server.

Is SQL Server REPAIR_ALLOW_DATA_LOSS safe? No, as its name says it loses data: it achieves consistency by deleting corrupt pages. If a backup exists, restoring from backup is always better. This command is run only when there is no backup, as a last resort, and always on a copy of the database files. Beforehand you should see the extent of the corruption with DBCC CHECKDB in emergency mode.

Ransomware encrypted my database files, can you recover them? This is not database corruption, it is a security incident. Encrypted MDF or ibd files cannot be brought back mathematically without the encryption key. What can be done is to look for unencrypted shadow copies, old backups or copies the attack missed, and to run incident response. Do not trust any method that promises decryption without the key.

Sources