Member Avatar for ilyons

Hey all,

Some rows of a single table in a database that I administer manage to have gone missing. Not that big of a problem: I have a backup.

The issue is that there has been more information added since the old data was lost, so I can't do a wholesale replacement. I've isolated the range of rows that were removed based on the primary key, but I'm not sure about the rest of the steps:

1) Export the rows based on the unique row id number;

2) Re-import the old rows without disturbing the new data.

Anyone have some wisdom for me?

Many thanks,
Ian

Edited by ilyons because: clarify row id #, not row #
  • 2 Contributors
  • 2 Replies
  • 330 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by ilyons

Dani AI

Generated

Good situation to have a backup — and a sensible plan already from . Below is a compact, safer workflow to restore only the missing primary-key range without disturbing newer rows. The emphasis is: work in a staging schema, list columns explicitly, verify, then apply.

  1. Restore the backup table into a separate schema (staging/backup). Confirm the schemas match (column names, types, defaults). Never use SELECT * for the final insert — list columns so you don’t accidentally shift data if schemas differ.

  2. Identify the rows that are in the backup but not in production. Example (inspect before you change anything):

SELECT b.*
FROM backup_db.mytable b
LEFT JOIN production.mytable p ON p.id = b.id
WHERE p.id IS NULL;
  1. Stage and insert only the missing rows inside a transaction. Example pattern (use the actual column list from your table):
START TRANSACTION;

INSERT INTO production.mytable (id, col1, col2, col3)
SELECT b.id, b.col1, b.col2, b.col3
FROM backup_db.mytable b
LEFT JOIN production.mytable p ON p.id = b.id
WHERE p.id IS NULL;

COMMIT;
  1. Safety checks and notes
  • Test the whole flow on a copy first (as plans).
  • If there are foreign keys, insert parent rows first or validate FK constraints before committing; avoid toggling FK checks unless you understand the consequences.
  • Watch triggers, unique constraints and auto-increment: after restore run SELECT MAX(id) and set AUTO_INCREMENT if needed.
  • Verify with counts/checksums and spot-check important rows.

If cross-table relations exist, create a staging table, validate referential integrity there, then apply to production. This minimizes risk and gives you a clear audit trail of what was restored.

Recommended Answers

All 2 Replies

Member Avatar for smantscheff

It all dependes. Does your table has relations to other tables? Is the primary key used as a foreign key elsewhere? Do you have orphaned records in relations?
If all this is no problem, the easiest way would be this:
Restore the backup table in a backup database (here called backup).
Then

insert ignore into mytable
select * from `backup`.mytable;

That way no new information will be overwritten, and all lost records will be restored.

Member Avatar for ilyons

Hey smatscheff,

This looks like it'll do the trick. I'll run it now on a test DB and see how that works out. Many thanks for your advice. I'll mark this solved as soon as I get the records copied over.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.