Restore Deleted Posts, Threads from Backup DB

[David Karol]

Thanks for the reply Lynne.

So here's how I got it all back?. (Hopefully other users can benefit from this.)

We make daily backups of the database. I took the backup from the morning before the user was banned. phpmyadmin was running extremely slow on the test server, so I did a little research to find a tool to use on my MacBook Pro. Looking at pro products only, I found Sequel Pro. The best part is that it's free! While I used Sequel Pro, this can all be done from phpmyadmin.

I created a new database on the same host / username as the test server's and then imported the backup there.

I went through the following tables and did a search for all rows with

• userid='<deleteduseridgoeshere>':
• thread
• subscribethread
• threadread
• postedithistory
• attachment

In each of these, I selected all of the search results, and then selected 'Copy as Insert Query.' I saved all of those queries in a text file for future reference.

Then I went through the sr_post table and searched for all rows with 'threadid' equal to the one's I just found with the banned userid. I copied all of those.

I went to the current test database and executed them all. After that I updated users' post counts in admincp. After testing each thread's functionality, I shut down the live site for about 20 minutes to execute the queries, update counters, and test functionality.

The only problem I had was in threadread, where I got an error saying PRIMARY KEY was a duplicate entry. I don't recall the specific numbers it mentioned, <userid>-****, but the 'threadid' that was referred two wasn't being touched at that point. I didn't look into this further, as it's not an important table to me.

There were no attachments in the removed threads by users other than the banned one. If that's the case, all rows in 'attachment' for each removed post will need to be copied over.

For reference, the queries went like this:

INSERT INTO `thread` (`threadid`, `title`, `prefixid`, `firstpostid`, `lastpostid`, `lastpost`, `forumid`, `pollid`, `open`, `replycount`, `hiddencount`, `deletedcount`, `postusername`, `postuserid`, `lastposter`, `lastposterid`, `dateline`, `views`, `iconid`, `notes`, `visible`, `sticky`, `votenum`, `votetotal`, `attach`, `similar`, `taglist`, `keywords`, `importthreadid`, `importforumid`, `vbseo_linkbacks_no`, `vbseo_likes`, `postercount`)
VALUES
(<values go here>)

That will change a bit for each table.