Restoring deleted posts from MYSQL backup?

[Joe Blow]

One of my moderators accidentally deleted 2,000 posts of a 3,000 post thread. I have a backup of the mysql database which contains these posts and was wondering if there was any way to restore them to the thread or are they now gone for good?

Any advice appreciated.

[richpal]

Contact your webmaster who hosts your site and ask him/her if they have a backup copy for you... Most hosts do carry a back-up but you need to act swiftly!!!

If they do, then copy it sharpish to your PC before the site is backed up again...

And sorry but I can't help you over vBulletin back-up info...

Sorry I can't help you more!

[Joe Blow]

No, unfortunately the posts were deleted last week.

Is there any way I can repair that particular thread using the information from an sql backup?

[Lats]

This sounded interesting, so I gave it a test

Extract the sql statements for the post table from your backup and create another table called post_copy. Delete all posts which don't have the relevant threadid associated with it -

DELETE FROM post_copy WHERE threadid != 12345

You should have around 3,000 posts in the post_copy table.

Then run this...

REPLACE INTO 
   post
SELECT
   * 
FROM 
   post_copy

I tried this on a much smaller post table however, it appeared to work well.

Please make a backup of your post table first.

[Joe Blow]

Hi Lats,

Thanks for the help mate.

The backup of the database is sitting on my harddrive as an sql dump. Do I need to restore the database to a server before working on it, or can I extract this data directly from the sql dump?

Is there a program that will allow me to do this?

Sorry for being such an sql n00b. I appreciate any advice you can offer.

[Lats]

Open that sql file in something like ultraedit which can handle large files okay, and search for CREATE TABLE post - change that to CREATE TABLE post_copy - delete everything before that.

Search for CREATE TABLE post_parsed - delete this and everything after that. What remains is the just the post table.

Find/Replace "INSERT INTO `post` VALUES" to "INSERT INTO `post_copy` VALUES"

Save it out as post_copy.sql and upload it to your server.

Log in to SSH and issue this command...

mysql -u[Username] -p[Password] [Database Name] < post_copy.sql

You should then be able to do the bits @ post #4.