*IMPORTANT* database rollback

[BuZz]

Due to a moderartion accident that involved three thread deletions, the forum database was rolled back to the last backup (18:04 GMT, 2014-01-03).

I have a backup from the database before the older backup was restored so I could manually restore the 2 posts and any PMs that were made between 6pm 2014-01-03 and 05:05 2014-01-04. Let me know if you want me to do this. Apologies for any inconvenience caused by this.

[jogglerhase]

Hi BuZz,
I am missing at least 1 important pm from hawsey from yesterday with some parcel tracking info - could you restore that?
Thanks for your help!
Heiner

[BuZz]

I have restored PM's to how they were as there were a a handful made since the earlier backup. Please confirm you have the missing PMs. thanks

[jogglerhase]

Yes - its there again!
Thank you, BuZz

[Error]

BuZZ, I,m missing the thread about 24/9696 playing files?
could it have something to do with the forum rollback?

http://www.jogglerwiki.com/forum/viewto ... ed+digital

this link

[BuZz]

Thanks for the heads up. Yep there have been more topics deleted in december also :/

I keep database backups for 8 weeks, so I will get an older database and attempt to reconstruct manually. this is going to take some time though.

[BuZz]

Ok, so not too bad to get the stuff back. I'll detail here for reference - might be useful to someone. I take backups very seriously, so getting the old database is not a problem. I use http://www.nongnu.org/rdiff-backup/ with 8 weeks of reverse increments (and 2 offsite backups a day).

A look at the moderator logs tells me the topics were accidently removed on 2013-12-28 in the afternoon. My backups run morning and evening, so I did

rdiff-backup -r2013-12-28T07:00:00 username@backupserver::path/to/database/backup.sql.gz /root/backup.sql.gz

I then loaded a temporary database with the old backup. A quick look at the phpbb schema and documentation tells me I need to restore

Code: Select all

phpbb_topics
phpbb_posts
phpbb_attachments

(excluding things like topic subscriptions etc which are not important)

the moderator logs tell me the topic title. So for each topic I did a

Code: Select all

select topic_id from temp.phpbb_topics where topic_title="title we want";
insert into liveforum.phpbb_topics select * from temp.phpbb_topics where topic_id=topic id above;
insert into liveforum.phpbb_posts select * from temp.phpbb_posts where topic_id=topic id above;
insert into liveforum.phpbb_attachments select * from temp.phpbb_attachments where topic_id=topic id above;

I also got the name of the attachments as I will need to restore them from backup also (not yet done)

Code: Select all

select physical_filename from temp.phpbb_attachments where topic_id

phew. everything seems ok. I will go through the moderator a log a bit more and make sure i didn't miss anything.

[BuZz]

All attachments have now also been restored.

[pete]

It was me that caused the grief / issues relating to posts being deleted.

I apologize about doing this.

Over the last month or so; with the holidays et al. I would visit the forum more relating to just a quick managing look rather than posting stuff.

That said I got a bit overzealous a few times seeing much spam and tried to delete all of the spam too quickly not looking at what I was doing (er deleting whole posts).

[BuZz]

No worries Pete. Accidents happen, and no permanent damage was done. Appreciate your help running the forum