SQL Database File Recovery: Solutions for Corrupted Databases

Understanding Database Corruption: Causes and Symptoms

Database corruption can manifest in various ways across different database systems. Before attempting recovery, it's important to understand what causes corruption and how to recognize it.

Common Causes of Database File Corruption

• Hardware failures: Disk failures, storage media degradation, RAM errors, and power outages during write operations
• Software bugs: Flaws in the database management system, operating system, or storage drivers
• Improper shutdowns: Forceful termination of database processes or system crashes during write operations
• Concurrent access issues: Multiple processes modifying the same data without proper locking
• Filesystem problems: Underlying filesystem corruption or incomplete write operations
• Storage space exhaustion: Running out of disk space during write operations
• Memory corruption: Buffer overflows or other memory-related errors affecting database operations
• Network interruptions: For distributed or replicated databases, network failures during synchronization

Symptoms of Database Corruption

• Error messages: Specific corruption-related error messages when accessing the database
• Application crashes: Programs terminating unexpectedly when accessing corrupt data
• Missing or inaccessible data: Tables, records, or entire databases that cannot be accessed
• Integrity check failures: Database consistency checks reporting errors
• Abnormal query behavior: Queries returning incorrect results or failing unexpectedly
• Index corruption: Database indexes producing incorrect query results or optimization issues
• Performance degradation: Sudden and significant slowdown in database operations
• Log file errors: Database logs showing recovery, transaction, or integrity issues

Identifying Corruption Severity

Database corruption can range from minor issues affecting a few records to catastrophic failures of entire database files. Understanding the severity helps determine the appropriate recovery approach:

1. Logical corruption: Issues within the database's internal structures while the physical files remain intact. Often repairable with built-in tools.
2. Physical corruption: Damage to the actual database files at the storage level. May require more specialized recovery techniques.
3. Metadata corruption: Damage to schema information, indexes, or system tables. Can sometimes be rebuilt from data.
4. Data page corruption: Specific data pages or blocks containing errors. May allow partial recovery of undamaged pages.
5. Systemic corruption: Widespread issues affecting multiple database components. Often requires restoration from backup.

Properly identifying both the cause and extent of corruption is the first critical step in successful recovery. In the following sections, we'll explore recovery techniques for specific database systems, ranging from built-in repair tools to advanced forensic recovery methods.

MySQL Database Recovery Techniques

MySQL and MariaDB offer several built-in mechanisms for diagnosing and repairing corrupted database files, with different approaches depending on the storage engine in use.

Diagnosing MySQL Database Corruption

Before attempting recovery, it's important to identify and assess the corruption:

1. Check Error Logs

sudo tail -f /var/log/mysql/error.log

Look for error messages indicating corruption, such as:

• "Table ... is marked as crashed"
• "Incorrect key file for table"
• "Can't find file"
• "InnoDB: Database page corruption"

2. Verify Table Integrity

mysqlcheck -u root -p --check --all-databases

Or for a specific database/table:

mysqlcheck -u root -p --check database_name table_name

3. Examine InnoDB Status (for InnoDB tables)

SHOW ENGINE INNODB STATUS\G

Look for sections indicating errors or corruption in the output.

Recovery Methods for MyISAM Tables

MyISAM tables are more prone to corruption but often easier to repair using built-in tools:

1. Basic REPAIR TABLE Command

REPAIR TABLE table_name;

This SQL command attempts to repair a corrupted MyISAM table.

2. Using mysqlcheck with Repair Option

mysqlcheck -u root -p --repair database_name table_name

This command-line tool offers more repair options than the SQL command.

3. myisamchk for Offline Repair

For more severe corruption, you may need to stop MySQL and use the myisamchk utility:

# Stop MySQL service
sudo systemctl stop mysql

# Basic repair
myisamchk -r /var/lib/mysql/database_name/table_name.MYI

# Recovery mode (for more severe corruption)
myisamchk -r -o /var/lib/mysql/database_name/table_name.MYI

# Force recovery (last resort, may lose data)
myisamchk --safe-recover --force --key_buffer_size=64M /var/lib/mysql/database_name/table_name.MYI

# Restart MySQL
sudo systemctl start mysql

The myisamchk tool works directly with the table files and offers more powerful recovery options for severely corrupted tables.

Recovery Methods for InnoDB Tables

InnoDB tables are more resistant to corruption but can be more challenging to repair when corruption occurs:

1. Using innodb_force_recovery

For InnoDB corruption, the primary approach is to use the innodb_force_recovery parameter:

# Edit my.cnf or my.ini and add this line:
innodb_force_recovery = 1

# Restart MySQL
sudo systemctl restart mysql

# Try to export your data
mysqldump -u root -p --opt database_name > database_backup.sql

# If level 1 doesn't work, try incrementally higher levels (2-6)
# Higher levels are increasingly aggressive and may cause data loss
innodb_force_recovery = 2
# ... up to ...
innodb_force_recovery = 6

The meaning of different recovery levels:

• Level 1 (SRV_FORCE_IGNORE_CORRUPT): Lets the server run even if it detects corrupt pages
• Level 2 (SRV_FORCE_NO_BACKGROUND): Prevents the main thread from running
• Level 3 (SRV_FORCE_NO_TRX_UNDO): Doesn't run transaction rollbacks
• Level 4 (SRV_FORCE_NO_IBUF_MERGE): Prevents insert buffer merge operations
• Level 5 (SRV_FORCE_NO_UNDO_LOG_SCAN): Doesn't look at undo logs
• Level 6 (SRV_FORCE_NO_LOG_REDO): Doesn't apply redo logs

2. Creating a New Database from Extracted Data

After extracting data with innodb_force_recovery:

# After setting innodb_force_recovery back to 0
# Create a new database
mysql -u root -p -e "CREATE DATABASE recovered_database"

# Import the salvaged data
mysql -u root -p recovered_database < database_backup.sql

3. Using Table Cloning for Partial Corruption

If only some tables are corrupted:

# Create a new table with the same structure
CREATE TABLE recovered_table LIKE corrupted_table;

# Copy good data from the corrupted table
INSERT INTO recovered_table SELECT * FROM corrupted_table WHERE ... /* conditions to filter out corrupted rows */;

Using Third-Party MySQL Recovery Tools

When built-in methods fail, commercial or specialized recovery tools can sometimes recover data from severely corrupted MySQL databases:

• MySQL Data Recovery: Specialized software for recovering data from corrupted .frm, .myd, .myi, and InnoDB files
• Stellar Phoenix Database Repair for MySQL: Commercial tool that can repair corrupt MySQL and MariaDB databases
• Percona Data Recovery Tool for InnoDB: Open-source tool focused on InnoDB table recovery
• DataNumen SQL Recovery: Specialized in recovering data from damaged MDF/NDF files

These tools often work by scanning the raw database files and reconstructing table structures and data, even when the database server cannot access them normally.

PostgreSQL Database Recovery Techniques

PostgreSQL's architecture provides good protection against corruption, but when it occurs, several techniques can help recover your data.

Diagnosing PostgreSQL Database Corruption

1. Check PostgreSQL Logs

sudo tail -f /var/log/postgresql/postgresql-[version]-main.log

Common corruption-related messages include:

• "could not read block ... in file"
• "invalid page in block"
• "incorrect checksum in block"
• "relation ... page ... is uninitialized"

2. Using Database Consistency Checking Queries

-- Check for invalid indexes
SELECT * FROM pg_class 
WHERE relkind = 'i' AND pg_get_indexdef(oid) IS NULL;

-- Check for pg_stat_activity consistency
SELECT pg_stat_reset();
SELECT pg_stat_get_backend_idset() AS active_backends;

3. Run REINDEX Operations to Detect Index Corruption

-- Try reindexing suspected tables or the entire database
REINDEX TABLE potentially_corrupted_table;
REINDEX DATABASE database_name;

If these operations fail with errors, they can help pinpoint corruption issues.

PostgreSQL Recovery Approaches

1. Using pg_resetwal (formerly pg_resetxlog)

For corruption in the transaction log (WAL):

# Stop PostgreSQL
sudo systemctl stop postgresql

# Run pg_resetwal
sudo -u postgres pg_resetwal /var/lib/postgresql/data

# Or with specific options
sudo -u postgres pg_resetwal -f /var/lib/postgresql/data

# Start PostgreSQL
sudo systemctl start postgresql

Warning: This approach can lead to data inconsistencies as it discards transaction logs. Use it only when other methods fail.

2. Recovering Individual Tables from Dumps

If you have a recent backup:

# Create a temporary database for recovery
createdb -U postgres recovery_temp

# Restore only the structure of the corrupted table
pg_restore -U postgres -d recovery_temp --section=pre-data --table=corrupted_table backup_file.dump

# Restore only the data of the corrupted table
pg_restore -U postgres -d recovery_temp --section=data --table=corrupted_table backup_file.dump

# Restore only the indexes and constraints
pg_restore -U postgres -d recovery_temp --section=post-data --table=corrupted_table backup_file.dump

# Export the recovered table
pg_dump -U postgres -t corrupted_table recovery_temp > recovered_table.sql

# Import into original database
psql -U postgres original_database < recovered_table.sql

3. Zero-Data-Loss Recovery with WAL (Write-Ahead Log)

If WAL archiving was enabled:

# Create a recovery.conf file in PostgreSQL data directory
sudo -u postgres nano /var/lib/postgresql/data/recovery.conf

# Add these lines
restore_command = 'cp /path/to/archive/%f %p'
recovery_target_time = '2025-05-17 14:30:00'

# Start PostgreSQL
sudo systemctl start postgresql

PostgreSQL will recover using archived WAL files up to the specified point in time.

4. Low-Level Forensic Recovery with pg_filedump

For severe corruption where other methods fail:

# Install pg_filedump
sudo apt-get install pg-filedump

# Examine a corrupted table file
pg_filedump /var/lib/postgresql/data/base/database_oid/file_oid

# Extract specific tuples (rows)
pg_filedump -D tuple_descriptor /var/lib/postgresql/data/base/database_oid/file_oid

This tool can help extract data directly from PostgreSQL data files, bypassing the database engine.

Third-Party PostgreSQL Recovery Tools

When built-in methods fail:

• PostgreSQL Database Recovery: Commercial tools specialized in recovering corrupted PostgreSQL databases
• Stellar Phoenix Database Repair for PostgreSQL: Specialized repair software for extracting data from corrupted PostgreSQL databases
• EaseUS Database Recovery Wizard: General database recovery tool with PostgreSQL support

These tools typically work by scanning database files and attempting to reconstruct the database structure and data from whatever readable information remains.

SQL Server Database Recovery Techniques

Microsoft SQL Server provides several built-in tools and methodologies for recovering from database corruption.

Diagnosing SQL Server Database Corruption

1. Run Database Consistency Checks

-- Basic consistency check
DBCC CHECKDB('DatabaseName');

-- More detailed output
DBCC CHECKDB('DatabaseName') WITH ALL_ERRORMSGS, NO_INFOMSGS;

This command checks all objects in the database for structural integrity.

2. Check SQL Server Error Logs

-- View SQL Server error log
EXEC sp_readerrorlog;

Look for entries containing terms like "corruption," "823," "824," or "825" error codes.

3. Check Specific Database Pages

-- Examine a specific page
DBCC PAGE('DatabaseName', file_id, page_id, print_option);

This can help identify corruption at the page level.

SQL Server Recovery Methods

1. Using DBCC CHECKDB Repair Options

SQL Server provides several repair options, with increasing levels of potential data loss:

-- Repair with no data loss (works for minor corruption)
DBCC CHECKDB('DatabaseName', REPAIR_REBUILD);

-- Repair with potential minimal data loss
DBCC CHECKDB('DatabaseName', REPAIR_ALLOW_DATA_LOSS);

Warning: REPAIR_ALLOW_DATA_LOSS can delete corrupted objects to make the database consistent. Always back up before using this option.

2. Restore from Backup with Standby Mode

-- Restore full backup
RESTORE DATABASE DatabaseName
FROM DISK = 'C:\Backups\FullBackup.bak'
WITH STANDBY = 'C:\Temp\Undo.dat';

-- Apply log backups sequentially
RESTORE LOG DatabaseName
FROM DISK = 'C:\Backups\LogBackup1.trn'
WITH STANDBY = 'C:\Temp\Undo.dat';

-- Continue with additional log backups as needed

This approach allows you to restore to a point just before corruption occurred.

3. Page-Level Restore for Targeted Corruption

If only specific pages are corrupted:

-- Identify corrupted pages
DBCC CHECKDB('DatabaseName') WITH TABLERESULTS;

-- Restore just the corrupted pages
RESTORE DATABASE DatabaseName
PAGE = '1:51, 1:52, 1:53'
FROM DISK = 'C:\Backups\FullBackup.bak'
WITH STANDBY = 'C:\Temp\Undo.dat';

This minimizes impact by only restoring the corrupted pages.

4. Emergency Mode Repair for Severe Corruption

-- Set database to emergency mode
ALTER DATABASE DatabaseName SET EMERGENCY;

-- Put database in single user mode
ALTER DATABASE DatabaseName SET SINGLE_USER;

-- Attempt repair
DBCC CHECKDB('DatabaseName', REPAIR_ALLOW_DATA_LOSS);

-- Return to normal mode
ALTER DATABASE DatabaseName SET MULTI_USER;

This is a last resort when a database won't come online normally.

Using SQL Server Data Recovery Tools

For severe corruption cases where built-in tools fail:

• ApexSQL Recover: Specialized tool for recovering data from corrupted SQL Server databases
• Stellar Phoenix SQL Database Repair: Recovers data from corrupted MDF and NDF files
• Recovery Toolbox for SQL Server: Repairs damaged SQL Server database files
• SQL Database Recovery: Extracts data from corrupted SQL Server databases

These tools can often recover data directly from MDF/NDF files even when SQL Server can't access them.

Advanced SQL Server Forensic Recovery

For cases of severe corruption:

1. Using the SQL Server Transaction Log

-- Examine transaction log content
SELECT * FROM fn_dblog(NULL, NULL);

This can sometimes recover recent transactions not yet written to data files.

2. Direct Data File Access Using Hex Editors

In extreme cases, specialized database forensic tools or hex editors can be used to examine MDF/NDF files directly, though this requires deep knowledge of SQL Server file formats.

SQLite Database Recovery Techniques

SQLite's single-file database design makes it both vulnerable to certain types of corruption and surprisingly resilient to others. Here are techniques to recover corrupted SQLite databases.

Diagnosing SQLite Database Corruption

1. Check Database Integrity

-- Using SQLite CLI
sqlite3 database.db "PRAGMA integrity_check;"

-- For a quicker but less thorough check
sqlite3 database.db "PRAGMA quick_check;"

A properly functioning database should return "ok". Any other result indicates corruption.

2. Check for Common Error Messages

Common SQLite corruption error messages include:

• "database disk image is malformed"
• "file is not a database"
• "unable to open database file"
• "database or disk is full"
• "disk I/O error"

SQLite Recovery Methods

1. Using the SQLite .dump Command

The most reliable method for SQLite recovery:

-- Attempt to dump the database to SQL statements
sqlite3 corrupted.db .dump > dump.sql

-- Create a new database from the dump
sqlite3 recovered.db < dump.sql

This approach extracts whatever data can still be read from the corrupted database.

2. Using the Recovery Extensions

For more severe corruption:

-- Using the recovery shell extension
sqlite3 corrupted.db
.recover | sqlite3 recovered.db

The .recover command is available in newer versions of SQLite and attempts to salvage as much data as possible.

3. External Recovery with SQLite Database Recovery

-- Using the sqlite3-recover tool (if installed)
sqlite3-recover corrupted.db recovered.db

This external tool can sometimes recover data from severely corrupted databases.

4. Recovery Using Journal or WAL Files

If SQLite was interrupted during a transaction, journal files may contain recoverable data:

-- Check for journal files
ls -la corrupted.db*

-- If you see .db-journal or .db-wal files, try a recovery utility that can
-- process these files, or simply try opening the database again (SQLite will
-- usually attempt recovery automatically)

Third-Party SQLite Recovery Tools

When built-in methods fail:

• DB Browser for SQLite: Has some recovery capabilities for damaged databases
• Stellar Phoenix Database Repair for SQLite: Commercial tool specialized in SQLite recovery
• SQLite Database Recovery Software: Extracts data from corrupted SQLite databases
• Recovery Toolbox for SQLite: Repairs damaged SQLite database files

Forensic Recovery for Severely Damaged SQLite Files

For extreme cases:

1. Hex editor analysis: SQLite has a relatively simple file format, and records can sometimes be extracted manually using a hex editor
2. File carving tools: Tools like PhotoRec can sometimes recover SQLite databases from raw disk images
3. SQLite header reconstruction: Recreating the SQLite file header to make a partially damaged file readable again

These approaches require technical expertise but can recover data when other methods fail.

Oracle Database Recovery Techniques

Oracle Database provides sophisticated recovery mechanisms through its Recovery Manager (RMAN) tool and additional options for corrupted database files.

Diagnosing Oracle Database Corruption

1. Check Alert Log and Trace Files

# View alert log
cat $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert/alert_$ORACLE_SID.log | grep -i corrupt

# View trace files
cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
ls -la *.trc | sort -k 6,7

2. Run Database Block Corruption Checks

-- Check for corrupt blocks
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

-- Run DBVERIFY utility
dbv file=/path/to/datafile.dbf blocksize=8192

3. Use RMAN Validation

-- Connect to RMAN
rman target /

-- Validate database
RMAN> VALIDATE DATABASE;

-- Validate specific datafile
RMAN> VALIDATE DATAFILE 4;

Oracle Recovery Methods

1. RMAN Block Media Recovery

For corruption limited to specific blocks:

-- Connect to RMAN
rman target /

-- Recover specific corrupt blocks
RMAN> RECOVER BLOCK DATAFILE 4 BLOCK 50;

-- Recover all corrupt blocks
RMAN> RECOVER CORRUPTION LIST;

This approach repairs individual corrupt blocks without restoring the entire datafile.

2. RMAN Restore and Recover

For more extensive corruption:

-- Connect to RMAN
rman target /

-- Restore and recover entire datafile
RMAN> RESTORE DATAFILE 4;
RMAN> RECOVER DATAFILE 4;

-- Restore and recover the entire database
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

3. Partial Database Recovery Using TSPITR

For recovering specific tablespaces:

-- Connect to RMAN
rman target /

-- Perform tablespace point-in-time recovery
RMAN> RECOVER TABLESPACE users UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS';

This recovers a specific tablespace to a point in time before corruption occurred.

4. Data Pump Export/Import for Logical Recovery

-- Export specific tables
expdp system/password TABLES=schema.table1,schema.table2 DIRECTORY=dump_dir DUMPFILE=recovery.dmp

-- Import to a new or repaired database
impdp system/password DIRECTORY=dump_dir DUMPFILE=recovery.dmp REMAP_SCHEMA=old_schema:new_schema

This approach works when you can still access the data but the database structure is damaged.

Oracle Advanced Recovery Techniques

1. Using db_block_checking for Prevention

-- Enable block checking
ALTER SYSTEM SET DB_BLOCK_CHECKING=FULL SCOPE=BOTH;

This helps detect corruption early, though it has a performance impact.

2. RMAN BLOCKRECOVER with CORRUPTION BLOCK

RMAN> BLOCKRECOVER CORRUPTION LIST;

This automatically identifies and recovers all corrupt blocks.

3. Using Flashback Technologies

-- Flashback the database to a time before corruption
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2025-05-17 14:00:00', 'YYYY-MM-DD HH24:MI:SS');
ALTER DATABASE OPEN RESETLOGS;

This works if Flashback Database is enabled and the corruption is recent.

Third-Party Oracle Recovery Tools

For cases when Oracle's built-in tools aren't sufficient:

• Oracle Database Recovery: Commercial tools specialized in recovering corrupted Oracle databases
• Stellar Phoenix Database Repair for Oracle: Repairs corrupted Oracle database files
• Recovery Manager for Oracle: Extracts data from corrupted Oracle database files

Preventing Database Corruption and Ensuring Recoverability

While recovery techniques are essential, preventing corruption and ensuring your databases are recoverable are even more important:

Implementing Robust Backup Strategies

1. Regular backups: Set up scheduled backups appropriate to your recovery point objective (RPO)
2. Backup verification: Regularly test backup integrity by performing test restores
3. Diverse backup methods: Combine full, differential/incremental, and transaction log backups
4. Offsite storage: Store backups in multiple locations, including offsite or cloud storage
5. Backup retention: Maintain multiple backup generations based on your retention policy

Hardware and Infrastructure Considerations

1. Use enterprise-grade storage: Invest in reliable storage systems with error correction capabilities
2. Implement RAID: Use appropriate RAID levels for database storage (RAID 10 is often recommended)
3. Uninterruptible power supplies: Protect against power outages with UPS systems
4. Regular hardware maintenance: Replace aging storage devices before they fail
5. Storage monitoring: Implement monitoring for early detection of storage issues

Database Configuration for Resilience

MySQL/MariaDB:

# In my.cnf or my.ini:
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_checksum_algorithm = strict_crc32

PostgreSQL:

# In postgresql.conf:
full_page_writes = on
wal_log_hints = on
checkpoint_timeout = 5min
max_wal_size = 1GB
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

SQL Server:

-- Enable page verification
ALTER DATABASE YourDatabase SET PAGE_VERIFY CHECKSUM;

-- Use full recovery model for important databases
ALTER DATABASE YourDatabase SET RECOVERY FULL;

SQLite:

-- Use WAL mode for better crash resistance
PRAGMA journal_mode = WAL;

-- Set synchronous mode for durability
PRAGMA synchronous = FULL;

-- Enable foreign key constraints
PRAGMA foreign_keys = ON;

Oracle:

-- Enable block checking
ALTER SYSTEM SET DB_BLOCK_CHECKING = FULL SCOPE=BOTH;

-- Enable block checksum
ALTER SYSTEM SET DB_BLOCK_CHECKSUM = FULL SCOPE=BOTH;

-- Enable Flashback Database
ALTER DATABASE FLASHBACK ON;

Operational Best Practices

1. Regular integrity checks: Schedule routine database integrity verification
2. Proper shutdown procedures: Always shut down databases cleanly
3. Transaction management: Use appropriate transaction isolation levels and avoid long-running transactions
4. Database maintenance: Regularly optimize, reorganize, and rebuild indexes
5. Version control for schema: Maintain schema definitions in version control
6. Monitoring and alerting: Set up comprehensive monitoring to detect early signs of corruption
7. Capacity planning: Ensure adequate storage space and prevent "disk full" scenarios

Creating a Database Recovery Plan

Prepare for corruption incidents before they occur:

1. Document recovery procedures: Create detailed recovery playbooks for different scenarios
2. Define roles and responsibilities: Clearly identify who does what during a recovery
3. Establish recovery time objectives (RTO): Define how quickly recovery must be completed
4. Set recovery point objectives (RPO): Define the acceptable data loss window
5. Regular recovery drills: Practice recovery scenarios to ensure team readiness
6. Maintain recovery tools: Keep recovery software updated and accessible
7. Document backup locations: Maintain clear records of where backups are stored and how to access them

Real-World Database Recovery Case Studies

Case Study 1: Recovering a Corrupted MySQL Production Database

Scenario: An e-commerce company experienced a server crash during peak sales hours, resulting in corruption of their MySQL InnoDB database. The corruption manifested as inability to access several critical tables, with error messages indicating page corruption.

Approach:

1. The DBA team first attempted recovery using the most recent backup, but it was 6 hours old and would result in significant data loss.
2. They then implemented a staged recovery approach using innodb_force_recovery:
1. Started with innodb_force_recovery=1 and attempted to export uncorrupted tables
2. Increased to innodb_force_recovery=4 when level 1 failed to provide access to some tables
3. Successfully exported most tables except for two severely corrupted ones
3. For the two inaccessible tables, they:
   1. Retrieved table structure from their schema versioning system
   2. Restored data for these tables from the 6-hour-old backup
   3. Recovered missing 6 hours of transactions from the binary logs
4. Created a new database instance and imported all recovered tables
5. Ran integrity checks to verify data consistency

Outcome: The database was restored with less than 10 minutes of data loss, significantly better than the 6 hours that would have been lost using just the backup. The company implemented more frequent backups and binary log archiving to prevent similar issues in the future.

Case Study 2: Recovering a Corrupted SQL Server Database with No Recent Backup

Scenario: A small business discovered their SQL Server database was severely corrupted after a power outage. The CHECKDB command showed extensive corruption, and their most recent backup was two weeks old.

Approach:

1. The initial attempt to repair using DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS would have deleted several critical tables.
2. Instead, they took a multi-faceted approach:
   1. Set the database to EMERGENCY mode to allow read-only access
   2. Used BCP (Bulk Copy Program) to extract data from tables that were still accessible
   3. For corrupted tables, used a third-party SQL Server recovery tool to directly read the MDF file
   4. Created a new database with the same schema
   5. Imported data from the extracted files and recovered tables
3. After recovery, they compared record counts with application logs to verify completeness

Outcome: Approximately 95% of the data was recovered. The business implemented a proper backup strategy with daily backups and transaction log backups every 15 minutes, plus database corruption checks as part of their maintenance plan.

Case Study 3: Recovering a Corrupted PostgreSQL Database

Scenario: A software company's PostgreSQL database became corrupted after a storage subsystem failure. The database would not start, reporting corrupted relation files.

Approach:

1. The team had WAL archiving enabled, creating an opportunity for point-in-time recovery.
2. They followed these steps:
   1. Restored the most recent base backup to a new server
   2. Created a recovery.conf file pointing to the WAL archive location
   3. Started PostgreSQL, which automatically applied WAL files to recover transactions
   4. After recovery completed, they verified data integrity using application-level consistency checks

Outcome: The database was completely recovered with zero data loss due to the continuous archiving of WAL files. The company implemented redundant storage and more frequent base backups to speed up future recovery if needed.