Skip to content
Data recovery lab · Denver, Colorado24/7 emergency line (720) 222-0110Check Case

Knowledge Base

Database

Databases and the files behind them: corruption, failed transactions and how database recovery differs from file recovery.

SQL Server Recovery

Microsoft SQL Server database recovery

SQL Server keeps a database in a primary data file, usually with the extension mdf, and a transaction log file with ldf. The log records every change in order, which is how SQL Server brings a database back to a consistent state after a crash. It is also what makes point-in-time restore possible.

The recovery model decides how much you can restore. Under the Full model, log backups let you recover to a chosen moment. Bulk-logged is similar with some limits on certain operations. Simple discards the log after each checkpoint, so you can restore only to the time of your last full or differential backup. Many accidental data losses come from a database running in Simple mode when the owner thought otherwise.

When a database is damaged, start from backups. If the log file is still readable, take a tail-log backup before anything else, since it captures changes made since the last backup. DBCC CHECKDB reports corruption. Its repair option, REPAIR_ALLOW_DATA_LOSS, is exactly what it says: it removes whatever it cannot fix, so it is a last resort and should be run on a copy.

If the disk failed, deal with the disk first. Copy the mdf and ldf files, and any other files in the same database, while the service is stopped. Keep every backup file, including old ones.

Advanced · sql server · transaction log · mdf

MySQL Recovery

MySQL database recovery techniques

MySQL stores data through storage engines, and the engine decides how recovery works. InnoDB, the default, keeps tables in files that end in ibd, along with a shared system tablespace and redo logs. It is designed to recover from a crash by itself: when the server restarts, it replays the redo log and rolls back unfinished transactions. MyISAM, an older engine, has no such log and can be left with damaged tables after a crash, which the REPAIR TABLE command can sometimes fix.

Backups make recovery straightforward. A logical dump from mysqldump or a physical backup can be restored, and the binary log, if enabled, records every change so the database can be rolled forward to a specific moment, for example just before someone ran a bad DELETE. Check whether binary logging was on, because it often is not.

When InnoDB won't start, the innodb_force_recovery setting, with values from 1 to 6, lets the server start in a limited mode so you can dump what is readable. Higher values risk more damage, and it should never be left on. Make a full copy of the data directory before trying it.

Don't delete the ib_logfile or ibdata files as a quick fix, since they hold information the tables depend on. If the disk is failing, image it first and work on the copy.

Advanced · mysql · innodb · myisam

Oracle Database Recovery

Oracle database recovery procedures

Oracle Database separates its data files, control files, and redo logs, and its recovery depends heavily on having them all. Redo logs record every change. In ARCHIVELOG mode, filled logs are saved as archived redo logs, so a backup can be rolled forward to a chosen point. In NOARCHIVELOG mode, you can only restore to the time of the last backup.

RMAN, Oracle's Recovery Manager, is the standard tool for backing up and restoring. It can restore a whole database, a single tablespace, a data file, or in some cases individual corrupt blocks while the database stays online.

Problems start when backups are missing, the control files are gone, or the archived logs have been deleted to free space. Without them, options narrow to extracting data from the data files directly, for example with third-party tools that read the Oracle block format and pull out tables.

For anyone responsible for an Oracle system, check that archive logging is on, that RMAN backups complete, and that a test restore has been done. If the database is down and the disk is questionable, copy all the files, including control files and redo logs, before opening it.

Advanced · oracle · rman · redo logs

PostgreSQL Recovery

WAL, base backups, and corruption

PostgreSQL writes every change to its write-ahead log, or WAL, before applying it to the data files. After a crash, it replays the WAL to reach a consistent state. With a base backup plus archived WAL, an administrator can restore to any point in time, such as just before an accidental table drop.

Trouble starts when the WAL is lost or deleted, when data files are damaged by a failing disk, or when someone resets the WAL to force the server to start. A reset can bring a database up with silent inconsistencies.

Before any repair, stop the server and copy the entire data directory, including the WAL. If the files came off failing storage, recover the storage first. Corrupted tables can often be partly salvaged at the page level.

Advanced · postgresql · wal · point-in-time recovery · database

SQLite Databases in Apps and Phones

The small database behind messages, browsers, and apps

SQLite is the embedded database behind countless apps: phone messages, browser history, contact lists, and many desktop programs. Each database is a single file, often accompanied by a write-ahead log file and a shared-memory file.

When records are deleted, SQLite often leaves their data in free pages or unused space within pages until it's reused or the database is vacuumed. The WAL file can also hold recent versions of pages, so copying a database without its WAL can miss the latest data.

For recovery or forensics, always collect the database together with its -wal and -shm files, and work on copies. Opening a database in a normal app can trigger a checkpoint that changes it.

Advanced · sqlite · wal · freelist · mobile apps

QuickBooks and Accounting Files

Company files, backups, and transaction logs

Desktop accounting software keeps your books in a company file, which for QuickBooks Desktop is a .QBW file, alongside backup files (.QBB) and a transaction log file (.TLG) that records changes since the last backup. The company file is a database, and it doesn't tolerate crashes or failing drives well.

When a company file is damaged, the built-in verify and rebuild tools fix many problems, but always copy the file first. If it came off a failing drive, recover the drive before running any rebuild.

Keep automatic backups on and store copies off the computer. A recent backup together with the transaction log can sometimes bring the books closer to the moment of failure.

Intermediate · quickbooks · accounting · company file · backup

Microsoft Access Databases

.accdb and .mdb files and Compact and Repair

Access databases are single files, .accdb or the older .mdb format, often shared over a network by several users. Network interruptions, crashes during writes, and many users on an old file format are common causes of corruption.

Access's Compact and Repair tool fixes many issues, but it rewrites the file and can drop records or objects it can't read. Always copy the file first. The lock file (.laccdb or .ldb) next to it shows who was connected.

Badly damaged files can often be partly salvaged by importing undamaged objects into a new database, or by extracting table data directly from the file's pages.

Intermediate · microsoft access · accdb · mdb · compact and repair

Exchange and Mail Server Databases

Mailbox databases, logs, and dirty shutdowns

On-premises Exchange servers store mailboxes in database files (EDB) with a series of transaction logs. After an unexpected shutdown, a database may be left in a dirty shutdown state and refuse to mount until logs are replayed.

A soft recovery replays logs and is safe. A hard repair forces the database into a consistent state by discarding what it can't fix, which can silently lose mail. It's a last resort, after a copy of the database and all its logs has been taken.

If the server's storage failed, recover the storage first, then the database. Individual mailboxes can be extracted from a damaged EDB with specialized tools when mounting isn't possible.

Advanced · exchange · edb · transaction logs · mail server

More categories