Restore SQL DB?

[AMVB]

Hi all

New to this. Managed to delete some forums accidentally (containing only a few posts but I want to restore them nonetheless).

Please can anyone assist in working out how to "cut over" the pertinent tables from the SQL backup file into a new up to date file, in order to replace the whole thing?

I know it's possible to telnet in and restore the SQL file but at the moment I haven't got a SQL front end and the file does crash when I try and read it on the server.

I don't tend to have much involvement with databasing so I'm not really that familiar with what is currently the best front end. I used mySQL Front a few years ago but again only briefly.

Any help, or a walkthrough, in restoring SQL db OR splicing/merging tables into one SQL file would be great. I suspect the merging option could be rather complex and protracted and may not be worth it just to restore a small amount of data, however it might prove useful in the future if I gain the knowledge now. I will build a test VB forum and try the restore options in there rather destroying the live environment!

I can see the logic behind not having a restore option in the control panel but it's a bit labour intensive not having ANY restore options. Or am I wrong and there are some sneaky restore functions there?

Many thanks for any help offered.

Regards

[jasonlitka]

I actually had a similar problem on one of my forums recently. One of my forum guys was moving posts around and accidentally deleted 3 forums that he thought were empty (but actually had about 5000 posts).

Unfortunately, unless the posts/threads were sequential, you're not going to be able to crop out what you need from your SQL dump. That said, all is not lost as there is actually a pretty easy (for me anyway) way to fix this assuming that you have an appropriate level of access to your server.

The first thing you'll need to do is to create a new database and restore the SQL backup to that new DB. You'll want to make sure that you have a mysql user that has access to both DBs, otherwise the queries you'll need to run won't work.

Once you've got that setup, what you'll want to do is run (3) queries. The first will insert the deleted forums back into the hot DB, the second will insert the deleted threads from those forums back into the hot DB, and the third will insert all of the posts from those threads back into the hot DB (which will take quite some time if you had a lot of deleted posts).

The three queries that you'll want to use are similar to those below (the assumption here is that you deleted forumid #'s 1, 2, & 3 from `hotdb`, that you restored your backup to `colddb`, and that you are NOT using any table prefix):

• INSERT IGNORE INTO `hotdb`.`forum` SELECT * FROM `colddb`.`forums` WHERE `colddb`.`forum`.`forumid` IN (1, 2, 3);
• INSERT IGNORE INTO `hotdb`.`thread` SELECT * FROM `colddb`.`thread` WHERE `colddb`.`thread`.`forumid` IN (1, 2, 3);
• INSERT IGNORE INTO `hotdb`.`post` SELECT * FROM `colddb`.`post` WHERE `colddb`.`post`.`threadid` IN (SELECT `threadid` FROM `colddb`.`thread` WHERE `colddb`.`thread`.`forumid` IN (1, 2, 3));

You should know that I've only done this once. While it was 100% successful for me, there is the possibility that it will go south for you, so you should create a backup of your current DB (don't overwrite the old backup) before you start, just in case.

[AMVB]

Thanks

Hiya

Many thanks for your advice and for taking the time to detail all of that.

The posts were sequential (I think!) and I am happy to try the database/query fix you suggested.

At the moment I don't have a front end for my mysql database and it is hosted by an external provider. I DO have backups of the database prior to losing the forums and I also have a backup taken after the forum loss.

In regards to the point you made about prefixes on the tables - here's where it might get a bit sticky. As a total newbie to this stuff, I had initially started off with SMF and migrated the entire SMF forum into VBulletin using Impex. After a bit of tweaking, the import worked very well. However my old tables were prefixed by SMF and in fact the lost forums were created in the old SMF board. It might become difficult if some of the stuff is indexed by SMF and some is not due to the cutover!

Which front end are you using?

Also, once the cold db is merged into the hot db, what is the easiest way to then administer this new database into the forum? I understand that it can't be done via the control panel, so is it just a case of telnetting it in?

Many thanks for your assistance.

[jasonlitka]

When you used Impex it should have copied that data into the proper tables for vBulletin. As long as your "backup before deletion" was from AFTER you used Impex, you shouldn't have a problem there.

What I meant by table prefix was whether or not you specified one in your config.php. If you did, it would change the names of the `forum`, `thread`, and `post` tables.

As to your last questions, I don't believe that you entirely understand the process. The `hotdb` mentioned is the one that is currently running your forums. This process will be copying the deleted data from the `colddb` (the one you used to restore your backup) to the `hotdb`. Once the process I described has been completed, there is no further need for the `colddb` and it can be deleted. There is no need for any change to the configuration of your site.

Finally, as to what front-end I use, I don't. I handle all of my DB access directly through the 'mysql' binary on the server.

[AMVB]

Hi again Jason,

Yes, I did make the backup quite a while after the Impex import so on that front everything is fine.

I see what you mean about table prefixes, I thought that potentially as I used Impex to import _SMF tables which were previously indexed as _SMF, that the tables might appear in the new db prefixed as _SMF, however upon checking the config file, no, they have no prefix.

I DO understand exactly what you mean by hot and cold d/bases, i.e. hot being live, cold being backup etc. But my approach was going to be, look in the cold db, find the tables showing the old deleted data I want and merge it into a realtime backup of the hot db ( just to capture the up to date info) - then upload this new db (after testing) straight back into live production. Whilst the board is in maintenance mode of course so nothing is lost.

As to administering everything through VB, in your opinion, what advantages does this provide over a front end?

Many thanks for your info.

Regards

[jasonlitka]

As to administering everything through VB, in your opinion, what advantages does this provide over a front end?

I don't administer everything through vB, I administer mysqld with the 'mysql' binary. I do this because there aren't any GUIs that can provide the type of queries I mentioned above in an automated fashion, and I can type most simple queries faster than I can find and click them in a GUI. I feel closer to my data and that's the way I like it.

[AMVB]

sql db

Agreed on being close to the data. Are you saying you just query the database directly then?

[jasonlitka]

Yes, I just query the DB directly. When I did the restore as mentioned above I didn't even turn the board off, there's really no need if you understand what each command you execute will do.

[Total666]

I have restored my complete database to the day before , One mod by mistake moved all the forums posts into 1 forum .. Neat . NOT ..

this works great after you get it all set up .. And will give you piece of mind ..

http://www.mysqldumper.de/

Total