CodeIgniter Database Utility atomicity and restore verification

Direct answer

Do not treat restore() as atomic. Use it only inside an explicit transaction when your driver and storage engine support transactional DML, and prefer a staging-restore-verify-promote pattern for large or DDL-heavy dumps. CodeIgniter has no built-in verifyRestore(), automatic checksum diff, or row-count diff against the source snapshot; use Query Builder and Database Forge methods plus native database checksum commands, with baseline values captured before the backup.

Confirmed facts

• CodeIgniter 3's Database Utility restore() runs backup SQL sequentially and does not wrap it in transStart()/transComplete().
• A mid-restore failure can leave partial schema or data; there is no automatic rollback across the whole file.
• MySQL DDL such as CREATE TABLE, DROP TABLE, and ALTER TABLE often commits implicitly and cannot be rolled back, especially on MyISAM. InnoDB and PostgreSQL are more transactional, but CodeIgniter's transaction abstraction still does not support nested transactions.
• CodeIgniter 4 may not expose the same backup()/restore() API; confirm the version before applying this pattern.

Steps for this case

1. Confirm framework version and engine. This answer assumes CodeIgniter 3 with MySQL InnoDB or PostgreSQL; for CodeIgniter 4 or MyISAM, adjust or use staging promotion.
2. Load the utility and begin a smart transaction before restoring:

$this->load->dbutil();
$this->db->transStart();
$this->dbutil->restore('/path/to/backup.sql');
$ok = $this->db->transComplete();
if (! $ok) {
    // log and alert; do not continue as if the restore succeeded
}

Inspect transStatus() after transComplete(). If it returns false, the transaction was rolled back for transactional statements; DDL that already committed implicitly may remain.

1. For large dumps, MyISAM, or files containing CREATE/DROP/ALTER, restore into a staging database or schema instead of the live database.
2. Verify staging, then promote by renaming tables or switching the active CodeIgniter database connection; keep the previous table as an archive until verification succeeds.

Verification after restore

Capture these values before the backup from the source, then repeat after restore and compare per table:

• $this->db->list_tables() confirms expected tables exist.
• $this->db->field_data('table') confirms columns and metadata.
• $this->db->count_all('table') detects missing rows.
• $this->db->query('CHECKSUM TABLE users') for MySQL-native checksum comparison; engine-specific and not available in all drivers.

Row-count comparison detects missing rows but not corrupted column values. There is no framework method that automatically diffs a restored database against its source snapshot; any comparison requires storing baseline values and comparing them after restore.

Missing detail that changes this recommendation

Are you using CodeIgniter 3 or 4, and which driver/storage engine are you using, such as MySQL InnoDB, MyISAM, or PostgreSQL?