No announcement yet.

Restore deleted thread from backup - summary and sanity check?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Restore deleted thread from backup - summary and sanity check?

    Requesting a sniff test on my plan below. FYI, relevant snippets from prior threads linked / summarized at end.

    My issue is a physically deleted, important thread.
    • Job at hand is to restore from backup through selective DB backflips, which I know just enough about to be dangerous.
    • REQEUEST here is for a sanity check, words of wisdom before I dabble.
    Here’s what I plan:
    A) - restore that backup to temp tables in the bb, e.g. <table>_restore, for manipulation and culling into current db. In particular, need the thread and post backup tables, made into temporary thread_restore and post_restore tables.

    (FYI, my lost thread is #745)

    B) from backup, manipulate through a three part process.

    B-1) Cull the herd in the restored db thread and post tables to leave only those items for restore:
    DELETE FROM post_restore WHERE threadid != 745
    DELETE FROM thread_restore copy WHERE threadid != 745

    B-2) Copy the left-over to-be-restored stuff into the live tables
    REPLACE INTO post SELECT * FROM post_restore
    REPLACE INTO thread SELECT * FROM thread_restore

    B-3) Clean up
    Delete post_restore and thread_restore tables
    Update forum counters

    Here’s what I see from these forums:

    Restore the backup to a new database. Browse the thread and post tables using a program like phpmyadmin. Prune all records in those tables except for ones with a threadid corresponding to the deletedthread. Make a data dump of those tables and restore that dump to your live database. Backup your live database before doing this.

    After the
    restore, you will likely need to rebuild your thread
    and forum info:

    Admin CP -> Maintenance -> Update Counters

    If the
    thread had attachments, this will not restore them. Look to the attachment table.

    You can restore the backup to a live database so you can view it using a program like phpmyadmin. Then find the thread's record in the thread table, and all of the thread's post's records in the post table and dump them to a sql file. Then run that dump file on your live database.

    There are other considerations like post attachments, unique recordids, etc. Backup everything first. You will probably need to update your various counters afterwards:

    Admin CP -> Maintenance -> Update Counters

    It is not advised to restore a partial backup since there are cross-references in other tables.

    Some SQL statements
    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...


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

    Please make a
    backup of your post table first.


    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.


    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.

  • #2
    All of those threads you referenced talk about similar methods of doing this. Basically you have to restore the deleted records, but this can be problematic since you can't easily restore all joined tables like attachments, subscriptions, etc.


    • #3

      My mod on forum using Physically Delete
      How can i restore it?



      • #4
        Use the same method discussed in the threads linked above. Or just restore a whole recent backup, thereby losing any recent posts since that backup was made.


        • #5
          How do I recover/restore a thread that a fellow administrator deleted directly from the board?


          • #6
            I managed to hard delete an important thread on my forum. Problem is, there hasn't been a backup done. Now I have a mirrored test forum running due to the upgrade of the vB software. I was wondering if it's possible to move the thread across by perhaps backing up the test forum and then recovering it on the "live" forum?


            • #7
              Once a thread is deleted from the database it is gone forever unless you restore a backup from before it was deleted. Restoring a backup results in the loss of all data since the backup was made.
              Kerry-Anne :)

              Twitter Blog



              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.