Jump to content

Lost database schema recovery help


pureless

Recommended Posts

I recently had a server crash happen to my hosting website where all data was lost. I had backups of all my websites, however there was one database i did not have backed up. The data is not as important to me as the schema is. I still obviously have all the php files associated with it with all the select/insert queries. I was wondering if there was an easy way to rebuild the schema (table and col names is all i need) based off the queries used. Ive tried going through the scripts by hand but it's a LOT of script and i can't seem to tell if i have all col's accounted for or how to tell what they were.

Link to comment
Share on other sites

To add to it, i have searched through the script looking for all variable arrays to get the col names but again its just a pain to figure out which table the col name is associated with etc, or telling if i have them all. Im just hoping someone has had this problem before or has an idea of how to go about rebuilding it.

Link to comment
Share on other sites

one way to do this is to run the script on a development system and see what database related errors there are, this of course assumes your database logic is testing for errors and reporting/logging them. references in the php code to the columns would produce php undefined variable/undefined array index errors.

 

if your code isn't testing for query errors, you could write your own database query function that does do this and simply do a global search/replace for what ever query function you are currently using and rename it to call the function you wrote.

 

your script shouldn't have a lot of different points where queries are being performed at. if you do, it might be time to simplify the code.

 

BTW - if you are using the mysql_ functions and are going through your code anyway, it might be a good time to update to the mysqli_ or PDO database libraries as the mysql_ functions are depreciated as of php5.5.

Link to comment
Share on other sites

Rebuilding from PHP code is a pretty difficult job. Most MySQL users use wonderful queries like SELECT * everywhere, so MySQL won't tell you which columns are missing, PHP just complains about missing indexes in arrays, somewhere.

 

So no, there is no easy way, it's manual labour looking at queries, looking at which fields in the resultarrays the script uses, hours of fun!

 

This kind of disaster is exactly why I insist on having (apart from backups and backups of backups)) a "structure.sql" file in the sourcecode somewhere, preferably with some test data.

 

PS:mac_gyver is right about refactoring, but do *NOT* do *ANY* of that until you have completely rebuilt the database an verified that it's correct. Making changes now will cause you to loose valuable information about the schema.

Edited by vinny42
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.