Partial Restore from DB Backup.... did I mess things up?

[Eolesen]

Hopefully I didn't break anything, but I just did a partial restore from yesterday's backup -- Ran a prune script which got a little too aggressive....

No issues manifesting just yet, but curious if this was the correct set of steps:


1) Restored yesterday's 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.

Right now, it looks OK, and I made sure to run this right after our nightly backups run so I have a good point-in-time restore if needed....

Also asking this on the VB support site, but they don't usually get into the weeds with database issues probably for fear people might mess things up....

[Eolesen]

Looks like I missed the attachments -- script updated above

I'm also guessing I could have done the INSERT IGNORE on the text and node tables...

[glennrocksvb]

The queries seem correct. But there might other queries needed such as for the likes (nodevote) and views (nodeview) tables and possibly the rest of the node**** tables.

[Eolesen]

Thanks, I'll add the other tables to the stored query for next time -- noderead and nodeview might matter in the future. The other node** tables are zero rows the way we use it.

I'd asked on the official forums, and got zero response.

Guessing they don't want to be on-record for telling users how to actually use their system beyond the constraints of the Admin CP.