Restoring a Site When the Files and the Database Are in Different Backups

A small WooCommerce shop I run the infrastructure for went offline when its old server went away. The contract ran out, the box was gone. I had a full Plesk backup of that server sitting locally, so I thought this would be a quick restore.

It wasn't. The files were in the full backup. The database wasn't. It only existed inside a VM backup on offsite storage, from a migration I had prepared earlier and never finished. So the restore turned into two digs in two different archives, plus a bit of glue to make them fit together again.

Same month, a second WordPress site from the same full backup had its data in a place I didn't expect either. Both cases taught me the same thing, so here they are together.

TL;DR: Restore the files first and read wp-config.php. It tells you the database name, user and table prefix, so you know exactly what to look for. Then search every backup you have for that database, and check siteurl inside each SQL dump before you trust it belongs to your site. Plesk keeps vhost files, subdomains included, in the domain-wide backup_user-data_* archive, not in the per-site one. If the DB and files come from different days, write down what's missing and tell the client.

Step 1: find the files

A Plesk full server backup is a big tar with a folder per domain inside. List it before you extract anything:

tar -tf server-full-backup.tar | grep -E '^domains/example\.com/' | head -50

In mine the interesting bits were:

domains/example.com/backup_user-data_*.tzst     # all vhost files of the domain
domains/example.com/databases/<dbname>_2/        # one folder per database
sites/shop.example.com/backup_pd_*               # per-site archive

The shop lived in a subfolder of the main domain's docroot (httpdocs/shop), so it was in the domain-wide backup_user-data archive. In my backup those .tzst files were zstd-compressed tar archives, so:

mkdir -p /srv/restore/files
tar -xf server-full-backup.tar 'domains/example.com/backup_user-data_*.tzst'
zstd -dc domains/example.com/backup_user-data_*.tzst \
  | tar -x -C /srv/restore/files httpdocs/shop

That gave me about 608 MB and 23,534 files. Theme, plugins, uploads, and the wp-config.php.

Step 2: read wp-config.php before you search for the database

grep -E "DB_NAME|DB_USER|DB_HOST|table_prefix|WP_HOME|WP_SITEURL" \
  /srv/restore/files/httpdocs/shop/wp-config.php

This is the map for everything that follows. Database name, user, prefix, and in this case WP_HOME and WP_SITEURL were set right there in the config. That turned out to matter later.

Then I looked for the database in the full backup. There were four WordPress database folders. None of them was the shop's. All four belonged to other sites on that server.

How I knew: a WordPress dump contains the site URL in the options table. Grep it out of every dump you find:

for f in /srv/restore/dumps/*.sql; do
  printf '%s: ' "$f"
  grep -o "'siteurl','[^']*'" "$f" | head -1
done

If the dump was made with extended inserts the whole options table is on one long line, which is why I use grep -o and not a plain grep. Four dumps, four other URLs. The shop's database simply wasn't in the full backup at all.

Gotcha: matching table prefixes don't mean matching sites

All four dumps used the default wp_ prefix, and so did the shop. If I had picked one by name or size and imported it, WordPress would have started fine with someone else's content. Always check siteurl (and home) inside the dump, not the file name.

Step 3: the database was in a VM backup

The one place that still had the shop's DB was a VM I had set up for a planned move to a self-hosted PaaS. I had dumped the shop database into a home directory on that VM and then the migration stalled. That VM's vzdump backup was on a Storage Box. The Proxmox host that made it was gone too, and the archive was missing a few percent at the end.

So I wrote my own VMA extractor and streamed the archive straight from the storage into it. The full story with the format and the offset traps is in extracting a Proxmox VMA backup without Proxmox. Short version:

ssh -p 23 u123456@u123456.your-storagebox.de "cat backups/vzdump-qemu-NNN.vma.zst" \
  | zstd -dc --long=31 \
  | python3 vma_extract.py /srv/restore/vm/

losetup -fP --show /srv/restore/vm/disk-dev1.raw
mount -o ro /dev/loop0p1 /mnt/vm
cp /mnt/vm/root/migrate/shop.sql /srv/restore/dumps/

Streaming mattered here. The box I was restoring onto had about 33 GB free, and the sparse disk image alone took 26 GB. The decompressed archive on top would not have fit. After copying the dump out I deleted the image right away to get the space back.

The dump was 5.5 MB. Same siteurl check as above, and this time it was the right one.

Step 4: import by hand when the panel is dead

The target was another Plesk box, but with an expired license. Sites and mail keep running in that state, but the plesk CLI refuses to do anything. I wrote about that in what still works when a Plesk license expires. So I created the database and user directly in MariaDB, using the Plesk admin password file:

mysql -uadmin -p"$(cat /etc/psa/.psa.shadow)" <<'SQL'
CREATE DATABASE shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'shopuser'@'localhost' IDENTIFIED BY 'the-password-from-wp-config';
GRANT ALL PRIVILEGES ON shopdb.* TO 'shopuser'@'localhost';
SQL

mysql -uadmin -p"$(cat /etc/psa/.psa.shadow)" shopdb < /srv/restore/dumps/shop.sql

Use the exact name, user and password from the rescued wp-config.php. Then you don't have to touch the config at all. A quick sanity check after the import:

mysql -uadmin -p"$(cat /etc/psa/.psa.shadow)" shopdb -e "
  SELECT post_type, COUNT(*) FROM wp_posts GROUP BY post_type;"

For me that showed 65 tables, 83 pages, 27 orders and 418 attachments. Numbers that look like a real shop, not an empty install.

Then files into the vhost, owner set to the subscription's system user, done:

rsync -a /srv/restore/files/httpdocs/shop/ /var/www/vhosts/example.com/httpdocs/shop/
chown -R sysuser:psacln /var/www/vhosts/example.com/httpdocs/shop

Gotcha: keep the old URL if it's hardcoded

WP_HOME and WP_SITEURL pointed to https://example.com/shop. I could have moved the shop to its own domain while I was at it, but then I'd have to search-replace the URL through the whole database, including serialized data. Keeping the same path meant zero changes. Restore first, move later if anyone actually wants it.

Verified with curl: /, /shop/, /wp-login.php, /wp-json/ and a few upload URLs all came back 200.

Gotcha: the files are newer than the database

The files were from the day the server died. The database dump was about three weeks older. That has consequences:

• Orders placed in those three weeks are gone. Not in the DB, not anywhere.
• Images uploaded in that window exist on disk but have no row in the media library. Harmless, just orphans.
• If plugins were updated in that window, the plugin code is newer than its tables. WooCommerce may ask for a database update in the admin. Take a fresh dump before you click it.

The first point is the important one. Tell the client exactly which date range is missing, so they can check it against their payment notifications. Don't hide that part.

The second site: files in the "wrong" archive

The other WordPress site from the same full backup was a different surprise. In Plesk it wasn't set up under its own domain. It ran in a subdomain vhost (site.example.com), with siteurl and home set to the real public domain.

The per-site archive sites/site.example.com/backup_pd_* was 179 bytes. It only had a stats marker in it. The actual files, all 675 MB of them, were again in the domain-wide domains/example.com/backup_user-data_*.tzst, in a subfolder named after the subdomain. The database was under domains/example.com/databases/.

So remember: Plesk keeps vhost files, subdomains included, in the domain-wide user-data archive. If a site folder looks empty, look one level up.

That site went into Docker on a different server instead of back into Plesk:

services:
  wp:
    image: wordpress:php8.2-apache
    entrypoint: ["apache2-foreground"]
    volumes:
      - ./html:/var/www/html
    ports:
      - "127.0.0.1:8082:80"
    depends_on: [db]
  db:
    image: mariadb:10.11
    environment:
      MARIADB_DATABASE: sitedb
      MARIADB_USER: siteuser
      MARIADB_PASSWORD: the-password-from-wp-config
      MARIADB_RANDOM_ROOT_PASSWORD: "1"
    volumes:
      - ./db:/var/lib/mysql
      - ./init:/docker-entrypoint-initdb.d:ro

The dump goes into ./init/init.sql. The MariaDB image imports it once, on the first start with an empty data directory. In wp-config.php I only changed DB_HOST to db and kept the original as a copy next to it.

Gotcha: override the WordPress image entrypoint

The official WordPress image has a setup script as its entrypoint that can copy WordPress core into /var/www/html and generate a config. With a restored docroot I want none of that. entrypoint: ["apache2-foreground"] skips the script and just starts Apache on the files I brought. The site was already set up to trust X-Forwarded-Proto, so it worked behind the reverse proxy without changes.

Test it before DNS moves, with the Host header against the local port:

curl -sI -H 'Host: www.example.org' http://127.0.0.1:8082/ | head -1
curl -s  -H 'Host: www.example.org' http://127.0.0.1:8082/ | grep -o '<title>[^<]*'

Then the Caddy block, then DNS. If you're moving DNS, lower the TTL first.

Gotcha: no mail after the restore

Both sites came back without working mail. The Docker image has no MTA, and on the Plesk box outbound port 25 was blocked by the provider. So password resets, contact forms and WooCommerce order confirmations silently go nowhere. Put an SMTP plugin with a real mailbox on the list right after the restore, or the first person to notice will be a customer.

What I'd do differently

The root problem: one backup had the files, another had the database, and neither was complete on its own. Since then I dump databases separately on a schedule and copy them offsite with the files, see rsync backups on Linux. A 5 MB SQL file next to the docroot is a lot easier to find than one inside an 80 GB disk image. And I check what's actually in a backup before I need it, which I wrote about in my backup post-mortem.

---

Related posts

• Apache as a Reverse Proxy for Docker Apps (and the Panel Trap)
• Rebuilding My Ghost Blog After the Database Was Gone
• The Idempotency Bug That Uploaded the Same File 153 Times