Restore Deleted Posts, Threads from Backup DB

[David Karol]

Hi,

We have a user that was banned in error from a modification. With that, all of his posts and threads were deleted.

Totals
11 Threads
57 Posts

We have a backup of the database though! Is it possible to merge the deleted threads/posts back into the current live database from the backup?

Also, there's nothing in moderation logs showing other users' posts as being deleted. Do those still exist in the current database? Will they be restored with threads? We're using vBulletin 4.2.

Thanks!
David

[Nelson58]

David,

If you have technical knowledge of SQL, yes.

I wanted something similar, to restore a user and posts and threads, but the mods that did that previously no longer work with 4.2 PL3. They tried to tell me how, but I lacked the technical knowledge to understand what they were saying.

Look at the threads here:

Here they try to tell me how to restore someone who is now a guest but I don't understand SQL. If you get it please share with me. Thanks.

https://vborg.vbsupport.ru/showthread.php?t=288639

These are old mods no longer work for this:

https://vborg.vbsupport.ru/showthread.php?t=236861&page=7

https://vborg.vbsupport.ru/showthread.php?t=147378



Nelson

[David Karol]

David,

If you have technical knowledge of SQL, yes.

I wanted something similar, to restore a user and posts and threads, but the mods that did that previously no longer work with 4.2 PL3. They tried to tell me how, but I lacked the technical knowledge to understand what they were saying.

Look at the threads here:

Here they try to tell me how to restore someone who is now a guest but I don't understand SQL. If you get it please share with me. Thanks.

https://vborg.vbsupport.ru/showthread.php?t=288639

These are old mods no longer work for this:

https://vborg.vbsupport.ru/showthread.php?t=236861&page=7
https://vborg.vbsupport.ru/showthread.php?t=147378

Nelson

Hi Nelson,

Thanks for the reply. My problem is that the posts and threads don't actually exist anymore in the live database. I need to pull those in from the backup database. I have a basic knowledge of SQL, however I'm not so comfortable with this.

Besides the post and thread tables, is there anything more that is needed?

[Lynne]

See this regarding restoring a thread:
http://www.vbulletin.com/forum/showthread.php?326281-Recreating-a-deleted-thread-number&p=1845274&viewfull=1#post1845274:

Try this, restore the backup to a different database and using the operations tab in phpmyadmin, rename the thread and post tables to something with a number on the end (post7 and thread7) then copy those 2 tables to the live database.
Run the following queries changing 777 to the threadid of the deleted thread.
Code:

INSERT INTO thread
(SELECT *
FROM thread7
WHERE threadid = 777)

Code:

INSERT INTO post
(SELECT *
FROM post7
WHERE threadid = 777)

As for restoring the user, you would need rows from the user table, userfield table and usertextfield table.

[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 (http://www.sequelpro.com/). 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.