When dealing with production databases, especially those copied from live systems, verifying file integrity becomes crucial. SQLite's durability makes corruption rare, but system crashes or improper copying can cause issues. The sqlite3 command-line tool provides several ways to check database integrity.

The most straightforward method is using SQLite's built-in integrity check:

sqlite3 your_database.db3 "PRAGMA integrity_check;"

This will return "ok" if the database is intact, or list specific problems if found. For a more thorough check:

sqlite3 your_database.db3 "PRAGMA quick_check; PRAGMA foreign_key_check;"

For maximum confidence, you can dump and reload the database:

# Create dump file
sqlite3 original.db3 .dump > database_dump.sql

# Verify by importing
sqlite3 new.db3 < database_dump.sql

This method catches any corruption that prevents proper reading of data.

Here's a Python script that performs comprehensive validation:

import sqlite3

def check_db_integrity(db_path):
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Run integrity checks
        cursor.execute("PRAGMA integrity_check;")
        integrity_result = cursor.fetchone()
        
        cursor.execute("PRAGMA quick_check;")
        quick_check_result = cursor.fetchone()
        
        if integrity_result[0] == 'ok' and quick_check_result[0] == 'ok':
            return True
        else:
            print(f"Integrity issues found: {integrity_result}, {quick_check_result}")
            return False
            
    except sqlite3.DatabaseError as e:
        print(f"Database error: {e}")
        return False
    finally:
        conn.close()

For very large databases, consider these options:
1. Use PRAGMA quick_check first (faster but less thorough)
2. Perform checks during off-peak hours
3. Verify individual tables with targeted queries

To avoid corruption in the future:
- Always use proper shutdown procedures
- Consider WAL mode for production systems
- Implement regular backup validation

When dealing with production SQLite databases (especially when copied from live systems), integrity verification becomes crucial. Common corruption scenarios include:

• Partial page writes during abrupt system shutdown
• Filesystem errors during copy operations
• Storage media failures
• Application bugs writing invalid data

SQLite provides a PRAGMA command specifically for integrity verification:

sqlite3 database.db "PRAGMA integrity_check;"

This performs a full scan of the database structure and returns "ok" if valid or detailed error messages if corruption is found. For a more thorough check:

sqlite3 database.db "PRAGMA quick_check; PRAGMA foreign_key_check; PRAGMA integrity_check;"

Here's a Python script that performs comprehensive verification and creates a clean copy if needed:

import sqlite3
import os

def verify_sqlite_db(db_path):
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Run all available checks
        cursor.execute("PRAGMA quick_check;")
        quick_check = cursor.fetchone()
        
        cursor.execute("PRAGMA integrity_check;")
        integrity_check = cursor.fetchall()
        
        cursor.execute("PRAGMA foreign_key_check;")
        fk_check = cursor.fetchall()
        
        if quick_check[0] != "ok" or integrity_check[0][0] != "ok" or fk_check:
            print(f"Database corruption detected in {db_path}")
            print("Quick check:", quick_check)
            print("Integrity check:", integrity_check)
            print("Foreign key violations:", fk_check)
            return False
        
        return True
    
    except sqlite3.DatabaseError as e:
        print(f"Database error: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()

def create_clean_copy(src_path, dest_path):
    try:
        # Use backup API for reliable copying
        src = sqlite3.connect(src_path)
        dest = sqlite3.connect(dest_path)
        
        with dest:
            src.backup(dest)
        
        return verify_sqlite_db(dest_path)
    
    except Exception as e:
        print(f"Error creating clean copy: {e}")
        return False
    finally:
        if 'src' in locals():
            src.close()
        if 'dest' in locals():
            dest.close()

For severely corrupted databases, consider these approaches:

1. Dump and Rebuild:

   sqlite3 corrupted.db .dump | sqlite3 new.db

2. Use the Recover Tool:

   sqlite3 corrupted.db ".recover" | sqlite3 recovered.db

3. Commercial Recovery Tools:
   • SQLite Database Recovery (https://www.sqlite.org/recovery.html)
   • Disksavers SQLite Recovery

When copying live SQLite databases:

# On Linux/MacOS:
sqlite3 live.db ".backup temp.db"
# Then copy temp.db instead of live.db

# Using VACUUM INTO (SQLite 3.27+):
sqlite3 live.db "VACUUM INTO 'backup.db';"

For production systems, consider enabling WAL mode for better concurrency and crash recovery:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;