Restoring WordPress Navigation from a Database Backup

Restoring an entire WordPress backup should be a last resort. A selective restore should be the default when something is missing or corrupt. Some things are easy to restore, like a deleted theme or plugin. If you know what you’re looking for, most content can be copied from a single database row found in a database backup file and then injected directly into a live site using PHPmyadmin.

What about the WordPress navigation? It looks simple however it’s stored across many different database tables and is difficult to restore without a guide. This is especially true for sites with large nested navigations. Let’s look at one approach using WP-CLI and some scripting. Note, if your theme uses the new navigation block, then the following won’t apply.

A very simple WordPress navigation

To begin we’ll need a database backup so that we can extract the rows needed for import. Here at Anchor Hosting we keep all previous daily backups. So grab just database-backup.sql from any previous backup that you wish to restore.

Longterm backup tab within Anchor Hosting

I wish the following step could be skipped however running SQL queries on a database backup file is not possible. To safely extract just what we need, I recommend importing our backup into a staging website. The staging site doesn’t need to look good as we can do everything through PHPmyadmin and WP-CLI. Select the database name on the top left and then select import. Now drag and drop the database backup here.

Next, connect over SSH to the staging site and run the following WP-CLI and bash commands to extract the database rows we’ll need. This assumes your staging site is using the same database prefix as your production site. If not, you’ll need to adjust to match the database prefix from the backup.

# Remove all existing .sql files
rm *.sql

# Grab current database prefix
prefix=$( wp db prefix )

# Export all nav_menu_item posts
wp db export --no-create-info=true --tables=${prefix}posts --where="post_type='nav_menu_item'" --single-transaction

# Export all postmeta for nav_menu_item posts
wp db export --no-create-info=true --tables=${prefix}postmeta --where="post_id in (select ID from ${prefix}posts where post_type='nav_menu_item')" --single-transaction

# Export all relevant taxonomies/terms/relationships
wp db export --no-create-info=true --tables=${prefix}term_taxonomy --where="taxonomy='nav_menu'" --single-transaction
wp db export --no-create-info=true --tables=${prefix}terms --where="term_id in (select term_id from ${prefix}term_taxonomy where taxonomy='nav_menu')" --single-transaction
wp db export --no-create-info=true --tables=${prefix}term_relationships --where="object_id in (select ID from ${prefix}posts where post_type='nav_menu_item')" --single-transaction

# Export theme mods which stores theme locations
wp db export --no-create-info=true --tables=${prefix}options --where="option_name LIKE '%theme_mods_%'" --single-transaction

# Combine all .sql files in one
cat *.sql > extracted_navigation.sql

# Replace "INSERT INTO" SQL statements with "REPLACE INTO"
sed -i "extracted_navigation.sql" -e 's#INSERT INTO#REPLACE INTO#g'

# Compress database export
zip -r extracted_navigation.sql.zip extracted_navigation.sql

# Remove all .sql files
rm *.sql

Next download /extracted_navigation.sql.zip from the staging site and import into PHPmyadmin on the production environment. With that, your navigation will be fully restored from backup.

A few other warnings:

  • This process works by restoring all database content to their original ID. That means you can’t use this process to move navigations between websites. It will only work to restore deleted or culprit navigations back to the original website.
  • This process is dirty and will also undo any other theme configurations that have changed since the backup was created and now.
  • Always backup your original website before making changes. A simple wp db export can save you from a simple mistake.