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.

[puertoblack2003]

so, whats the exact instruction to perform this task i have a test db, from one db1 to another db2

[Eolesen]

All of the steps are in the first post. In my script, "Live" is the production database, and "Yesterday" is copy that was restored from a SQL backup.

If you don't know how to do the database create/restore, you probably shouldn't be attempting the scripts, either.

[puertoblack2003]

well, the need to ask was it looks gibberish and messy to me. my crystal ball was not able to tell me that live mean production and yesterday is copy.. You offered your opinion at vbulletin, I know enough to fix any issue within the database..

[Eolesen]

Yeah, it's gibberish, but I had been looking at the problem for about 12 hours when I originally wrote that up...

No offense intended with the comments on DB skills -- the average VB admin can't handle much beyond what's on the ACP.

I tend to do very little in the ACP, and most of my updates are done directly on the database. The lack of support from the folks at VBulletin on the database side of things gets really frustrating.