Restoring Deleted Forum Posts

[eolesen]

I asked this on the "official" support site and got zero responses but a bunch of views.... so I'll give it a try here.

A few days back, I ran a prune script which got a little too aggressive due to a typo, and posts from 400 days back got whacked instead of 4000 days back.

It doesn't appear that I broke anything yet, but I did a partial restore from the previous day's backup by running thru the following steps:


1) Restored the previous MySQL backup of the live database (live) to a separate database (yesterday)

2) Ran the following database script:

insert ignore into live.closure      SELECT * FROM yesterday.closure;

INSERT INTO live.text                SELECT * FROM yesterday.text    WHERE nodeid not in   (select nodeid from live.text);

INSERT INTO live.node                SELECT * FROM yesterday.node    WHERE nodeid not in   (select nodeid from live.node);

INSERT IGNORE INTO live.attach       SELECT * FROM yesterday.attach;

INSERT IGNORE INTO live.filedata     SELECT * FROM yesterday.filedata;
​

​
3) Ran Maintenance -> General Update Tools --> Update Topics

4) Ran Maintenance -> General Update Tools --> Update Forums


Closure has a unique identity key built on parent-child, so the INSERT IGNORE seems to have avoided any duplication. I tried to do a subquery but it wasn't giving a reasonable amount of rows as a result set, so I went with brute force....

The nodeid subquery for Text and Node seems to have also been surgical and only restored what had been deleted.

I made sure to run the script right after the daily backups ran so I had a good point-in-time restore if needed....

~72 hours later, I have normal activity on the forums, and nobody has reported any anomalies, which is a good sign.

Digging into the forums where the deletes took place, it looks like everything restored including attachments.

My counts before and after also look about right for what was pruned:

Topics: 116,016 Posts: 1,218,561
Topics: 151,626 Posts: 1,392,590

Fully realizing this is a "do at your own peril" action, does this look like the right steps or am I missing something else?

Glenn V suggested maybe including the nodeview and noderead tables. Since the threads I restored were all over a year old, I wasn't too concerned with having those values restored but it might make a difference if it were more recent threads being restored.