Wednesday, February 27, 2013

Recovering auto-pruned posts in PHPbb forums...from SQL file backups, instead of the internal PHPbb tool

I've been using PHPbb since V2 It's an excellent tool if you want to create a standard forum without a fuss in record time. I recon that I«ve been off for a while since V2 , but recently i had the need to create 2 sites that, according to specs where just simple forums, so I returned to PHPbb and found my self on V3.
During the rush of putting the sites up A.S.A.P /BTW with install, full config, sample posts, creating emails invite emails, changing the email templates and code to include registration custom fields and template design and implementation of template design with google adds code embed, it took-me a total of 2 hours to get everything done. OOhh and it's 2 hours for the 2 forums, not 2 hours each! Not you have to respect that. Free code that saves you countless ours of coding and templating and configuring time. And mind that I was "rusty" (I didn't PHPbb since V2 and a lot has changed). This, is productivity that HAS to be appreciated: THANKS PHPbb team.

I decided to post this article after finding some misleading information on the PHPbb forums stating (and I quote): "to script to do that automatically has been created yet", "it's just too complex, better restore the entire database".

OK, you have instructions on how to restore and backup de database using PHPbb internal tools (makes sense as it IS the supported feature) but let's just assume that, just like me, you have much more to deal with and you configure your servers to backup automatically using cpanel, for instance.
That means that you will not be using the internal PHPbb backup tool and as a result you'll be able to restore or backup at MySQL (or your db of choice) files level. That is also a good way to work right until the point where you find out that you left "autoPrune = yes" on one of your forums.
You see Auto  Prune is a nice admin tool. Let's just say for instance that you create a forum for improvement requests... you want to have people writing there, but you don't want those posts to stay there for ever. They just loose sense when either you implement, or reject the improvements users request.
However if you just enable it in one of your other content forums, and please mind that by default it is set to prune posts over 7 days with no activity, you might just find your self visiting a ghost forums (especially if you are still starting it and you have few people writing on it.

Now picture you forget that setting on, and also use Cpanel to backup. Well than I guess you'll find your way into this post (or the other forums posts I've quoted") sooner or later.

It's easy to restore your pruned posts if you have an SQL for for your database restore.
Just open your administration tool (PHPMyadmin for instance). At the same time, open your SQL restore file on a text editor and then browse the tables and the respective restore code to check what has been deleted and out of those what do you want to restore.
Then use an SQL tool to run commands on your server and copy-past from the backup, file, clean up unwanted lines and run the command.
It's that simple. The ONLY thing you absolutely need to understand is that there are several tables that have post information and indexing to forums and topics.
So you'll have to perform this procedure in the following tables (and add the full lines to the missing table):
   - PHPbb_posts
   - PHPbb_topics
   - PHPbb_topics_posted

This will render your posts back on to your forum. However, you you also want the forum statistics and last post and poster up-to-date, you need to restore a part of your forums table. So inside PHPbb_forums, find the following fields and restore them from your backup (probably using either an update command or simple copy-paste into location, depending on just how much you need to restore)
   - forum_posts
   - forum_topics
   - forum_topics_real
   - forum_last_post_id
   - forum_last_poster_id
   - forum_last_post_subject
   - forum_last_post_time
   - forum_last_poster_name
   - forum_las_poster_colour

When you finish this procedure, now only you've recovered your posts and topics, you have also restored those statistics indicators on the forum's front page that will point your users to the most active topics.

That's it, that's all. Simple and not by all means in need of a script or a complicated procedure.