Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 26 Jan 2013, 21:50
David Karol David Karol is offline
 
Join Date: Jan 2011
Question Restore Deleted Posts, Threads from Backup DB

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

Last edited by David Karol; 26 Jan 2013 at 21:51. Reason: Add vBulletin Version
Reply With Quote
  #2  
Old 27 Jan 2013, 05:37
Nelson58 Nelson58 is offline
 
Join Date: Oct 2011
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.

http://www.vbulletin.org/forum/showthread.php?t=288639

These are old mods no longer work for this:

http://www.vbulletin.org/forum/showt...=236861&page=7

http://www.vbulletin.org/forum/showthread.php?t=147378



Nelson
__________________
Nelson
www.Hobby-Machinist.com
Reply With Quote
  #3  
Old 27 Jan 2013, 17:53
David Karol David Karol is offline
 
Join Date: Jan 2011
Originally Posted by Nelson58 View Post
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.

http://www.vbulletin.org/forum/showthread.php?t=288639

These are old mods no longer work for this:

http://www.vbulletin.org/forum/showt...=236861&page=7
http://www.vbulletin.org/forum/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?
Reply With Quote
  #4  
Old 27 Jan 2013, 19:31
Lynne's Avatar
Lynne Lynne is offline
 
Join Date: Sep 2004
Real name: Lynne
See this regarding restoring a thread:
http://www.vbulletin.com/forum/showt...1#post1845274:
Originally Posted by Lats
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.
__________________
Former vBulletin.org Staff Member

Try a search before posting for help. Many users won't, and don't, help if the question has been answered several times before.
W3Schools -
Online vBulletin Manual
If I post some CSS and don't say where it goes, put it in the additional.css template.
I will NOT help via PM (you will be directed to post in the forums for help.)
Reply With Quote
  #5  
Old 28 Jan 2013, 03:45
David Karol David Karol is offline
 
Join Date: Jan 2011
Thumbs up Restore Deleted User's

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:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


That will change a bit for each table.
Reply With Quote
Reply

Similar Threads
Thread Thread Starter Forum Replies Last Post
Administrative and Maintenance Tools Restore Posts and Threads of Deleted User. borbole vBulletin 3.8 Add-ons 26 15 Apr 2014 18:09
Administrative and Maintenance Tools Restore Posts and Threads of Deleted User. (New Version) borbole Modification Graveyard 100 01 May 2012 21:57



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 16:45.

Layout Options | Width: Wide Color: