A couple of days ago I made a mistake on my blog and accidently overwrote one of my posts, it was whilst using the excellent Windows Live Writer and was absolutely nothing to do with the software, it was entirely a PEBKAC issue.
The mistake was that I took an existing post (first part in a multipart series) and edited it to make the second part of the article (I am used to Word documents). In this case it was only after I posted up the draft to the website did I realise what I had done.
Not wanting to start the work again I decided to see if it was possible to restore the site (I take twice daily backups using the excellent EZPZ OCB and Dropbox) so I had no concerns about the data actually being permanently lost, more just concerns about how to restore it without upsetting my current blog contents.
Looking around for a few solutions I found the excellent BitNami offerings and started looking at their various stacks\virtual machine offerings.
First of all I tried using their dedicated WordPress VM using the Ubuntu flavoured distribution (OpenSuse Linux is also offered). With that I had to configure SSH and enable phpMyAdmin pages to allow admin from IP addresses other than 127.0.0.1, once I had it configured and working correctly I then attempted to import my EZPZ OCB files and site (I first replicated my MySQL db environment and changed various conf\php files where it referenced my live DB environment) but I never managed to get it to work correctly and I didn’t want to spend too much time trying to work out why.
I next moved onto the WordPress Windows Stack, essentially this is a WAMP (Windows, Apache, MySQL and PHP) stack that’s based around the WordPress application. This made basic administration of the phpMyAdmin page a lot easier (although my NANO skills are definitely improving) I still couldn’t get a restore of my site working correctly.
In the end I found a much easier method of restoring a deleted post and that’s from directly within my existing MySQL database.
Just by chance I had been looking at ways of reducing the size of the MySQL export as there is a 2mb limit when importing MySQL databases. I started looking at what would happen if I just reduced what I was exporting and that’s when I discovered that for all revisions done to every post the site keeps revision entries in the database. Just because I had overwritten the original post didn’t mean that the original post’s contents had been completely deleted.
The following instructions show how I restored my missing post.
Instructions
Launch the phpMyAdmin console (in my case I use 1and1 for hosting purposes so I did this through their web control panel).
Here you can see the various tables in your MySQL db. Find the table relating to posts and click on it.
You should then be presented with the above page, click on Browse.
If your site has a large number of posts you may want to increase the amount of rows that you’re looking at, originally this is set to 300, above you can see I have increased this to 1000.
To make life easier click on the post_title tab and this will put the rows into order.
Browse down to your missing post, you will see that there are various revisions available.
Choose the correct date and then click on the pencil (edit).
Now you can see the original content of the missing post, simply copy the content over to your site or blogging application and you’re good to go. 
thank you, thank you, thank you, that’s a life saver!
I’ve just had a problem with a new plugin that happened to delete ALL my posts! Unfortunately, like an idiot, I hadn’t taken a backup before installing it.
I was sweating under the pressure of having to rewrite everything until I found your blog.
Your clear instructions have enabled me to restore all the missing posts. Thanks again.
WOW thanks a lot dude, you are the man… ive almost give up