Missing Database Tables After Backup Restore – Fix

30-Second Fix – Check the Table Prefix

Nine times out of ten, the culprit here is a table prefix mismatch. You restored the database, but your app is looking for wp_ tables and your backup had myblog_.

Open your app's config file—wp-config.php for WordPress, app/config/database.php for Laravel, etc. Look for the $table_prefix or 'prefix' line. Compare it to what's actually in the database.

# Quick check in MySQL
mysql> SHOW TABLES FROM your_database_name;

If the prefixes differ, you've got two options:
1. Update the config to match the backup's prefix.
2. Rename the tables. I use this one-liner in MySQL:

RENAME TABLE old_prefix_users TO new_prefix_users;

But that's tedious if you have 50+ tables. Faster: dump the backup SQL, search-replace the old prefix with the new one using sed, then re-import. You can find the old prefix by grepping the SQL file:

grep 'CREATE TABLE' backup.sql | head -5

If the prefixes match and tables are still missing? Move to the next step.

5-Minute Fix – Verify the Backup Was Complete

Partial restores happen more often than people admit. Maybe the backup script timed out, or you grabbed the wrong file from the server. Don't assume the backup was full.

Check file size. A WordPress database with 10 tables should be at least a few MB. If your SQL file is 200KB for a site with 50,000 posts, that's suspicious. Open the file and look at the last line—it should end with a Dump completed comment if it came from mysqldump.

tail -5 backup.sql

If you see partial SQL or it cuts off mid-statement, the backup's corrupt. Grab a fresh one or try the host's backup tool—cPanel, Plesk, or WHM usually have their own database backup feature under phpMyAdmin or Backup section. Those tend to be more reliable than custom scripts.

Also: check if the backup includes CREATE DATABASE statements. If you restored into an existing database without dropping it first, old tables might have stayed, and new ones got skipped on conflicts. Drop and re-create the database cleanly before importing:

mysql -u root -p -e 'DROP DATABASE IF EXISTS your_db; CREATE DATABASE your_db;'
mysql -u root -p your_db < backup.sql

Still missing tables? Time to get surgical.

15+ Minute Fix – InnoDB Corruption or Incomplete Transaction Log

This is the advanced scenario—you've got the right backup, the prefix matches, but some tables just refuse to show up. Usually happens with InnoDB tables when the backup was taken while a transaction was running. Or the SQL file contains DROP TABLE IF EXISTS statements that ran, but the CREATE TABLE didn't execute due to a syntax error or foreign key constraint failure.

Step 1: Check for SQL errors during import. Re-run the import with verbose output and capture it to a file:

mysql -u root -p your_db < backup.sql 2>&1 | tee import_errors.log

Look for lines like ERROR 1050 (table already exists) or ERROR 1215 (foreign key fails). If you see foreign key issues, the order of table creation matters. You can prepend SET FOREIGN_KEY_CHECKS=0; at the top of the SQL file to bypass those.

Step 2: Check InnoDB data dictionary. Tables that exist in the file system but not in the information schema are a sign of corruption or partial recovery. Run:

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db';

If a table shows as NULL for ENGINE, InnoDB doesn't recognize it. You'll need to recreate the table structure from the backup and then re-import the data. Here's the blunt truth: if the table definition is missing from the backup SQL—like when someone took a backup with --no-create-info—you're out of luck unless you have the schema somewhere else (like a migration file or ORM models).

Step 3: Extract individual table definitions. Use sed or awk to split the SQL file into separate table files. Then create each missing table manually:

awk '/CREATE TABLE.*`missing_table`/,/;/' backup.sql > create_missing.sql
mysql -u root -p your_db < create_missing.sql

Then insert the data rows for that table from the same backup—you'll find them in INSERT INTO missing_table blocks.

If none of this works, the backup is toast. Restore from a different backup point, or—if you have a full server snapshot—restore the entire database directory from /var/lib/mysql/your_db/. Don't just copy files in unless you stop MySQL first, or you'll corrupt the whole thing.

Last resort: contact your host and ask if they have a server-level snapshot or an older backup in their retention. I've seen hosts keep 7-30 day rollbacks. That has saved more than one client's site.