Solved

I backed up a table creating an identical table and in original table mistakenly deleted columns, so how can I restore/update?

Posted on 2014-02-19
9
243 Views
Last Modified: 2014-03-24
CREATE TABLE `email_doc` (
  `id` int(11) NOT NULL auto_increment,
  `unix_timestamp` bigint(20) default NULL,
  `from_email` varchar(200) default NULL,
  `from_name` varchar(200) default NULL,
  `to_email` varchar(200) default NULL,
  `to_name` varchar(200) default NULL,
  `subject` varchar(400) default NULL,
  `body` varchar(4000) default NULL,
  `real_id` varchar(30) default NULL,
  `checked` int(11) default NULL,
  `me_description` varchar(9000) default NULL,
  `client_description` varchar(4000) default NULL,
  `sms_type` tinyint(4) default NULL,
  `client_start` datetime default NULL,
  `client_end` datetime default NULL,
  `client_total` int(11) default NULL,
  `me_start` datetime default NULL,
  `me_end` datetime default NULL,
  `me_total` int(11) default NULL,
  `real_timestamp` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unix_timestamp` (`unix_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=332 DEFAULT CHARSET=utf8;


CREATE TABLE `email_doc_bkp` (
  `id` int(11) NOT NULL auto_increment,
  `unix_timestamp` bigint(20) default NULL,
  `from_email` varchar(200) default NULL,
  `from_name` varchar(200) default NULL,
  `to_email` varchar(200) default NULL,
  `to_name` varchar(200) default NULL,
  `subject` varchar(400) default NULL,
  `body` varchar(4000) default NULL,
  `real_id` varchar(30) default NULL,
  `checked` int(11) default NULL,
  `me_description` varchar(9000) default NULL,
  `client_description` varchar(4000) default NULL,
  `sms_type` tinyint(4) default NULL,
  `client_start` datetime default NULL,
  `client_end` datetime default NULL,
  `client_total` int(11) default NULL,
  `me_start` datetime default NULL,
  `me_end` datetime default NULL,
  `me_total` int(11) default NULL,
  `real_timestamp` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unix_timestamp` (`unix_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=332 DEFAULT CHARSET=utf8;

Open in new window



exact same table structure in email_doc_bkp
which is good that I decided to create a backup table because I deleted all these columns from email_doc

  `me_description` varchar(9000) default NULL,
  `client_description` varchar(4000) default NULL,
  `sms_type` tinyint(4) default NULL,
  `client_start` datetime default NULL,
  `client_end` datetime default NULL,
  `client_total` int(11) default NULL,
  `me_start` datetime default NULL,
  `me_end` datetime default NULL,
  `me_total` int(11) default NULL,


so how can I repopulate email_doc
restore or update?
0
Comment
Question by:rgb192
9 Comments
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39872171
Did you backup the table structure and data? If so, temporarily rename the currently defective table so it is safe and out of the way. Copy and paste the SQL backup right into a SQL execution in phpMyAdmin or another admin tool you might prefer. Voila... Your table and data will be restored to the time you did the backup.

If your SQL data to too big to copy and paste, you can also run the SQL from a file for the same results.

If you did not backup the data and only the structure, you are not going to be a happy camper... Someone else might chime in but you are probably not going to be able to recover the missing column data.
0
 

Author Comment

by:rgb192
ID: 39872437
I have inserted new rows to email_doc which are after the email_doc_bkp create and insert

but I need the old rows of email_doc_bkp that have all the columns because I deleted all the fields in 9 columns in email_doc

so maybe update from email_doc_bkp to email_doc
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39872708
This is what I would do:
1. rename email_doc to email_doc_bad
2. rename email_doc_bkp to email_doc
3. from email_doc_bad, insert the rows with an id not present in email_doc. Something like:
INSERT INTO email_doc (`id`, `unix_timestamp`, `from_email`, `from_name`, `to_email`, `to_name`, `subject`, `body`, `real_id`, `checked`, `real_timestamp`)
    SELECT `id`, `unix_timestamp`, `from_email`, `from_name`, `to_email`, `to_name`, `subject`, `body`, `real_id`, `checked`, `real_timestamp`
        FROM email_doc_bad 
        WHERE `id` NOT IN (
        SELECT `id` FROM email_doc)

Open in new window

You probably can do this faster using a INSERT IGNORE, but the version with the subquery is easier to read.

HTH,
Dan
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39872744
Assume you've already recreated the dropped windows.

Try this:

update email_doc, email_doc_bkp set
   email_doc.`me_description` = email_doc_bkp.`me_description`, 
  email_doc.`client_description` = email_doc_bkp.`client_description`,
  email_doc.`sms_type` = email_doc_bkp.`sms_type`,
  email_doc.`client_start` = email_doc_bkp.`client_start`,
  email_doc.`client_end` = email_doc_bkp.`client_end`,
  email_doc.`client_total` = email_doc_bkp.`client_total`,
  email_doc.`me_start` = email_doc_bkp.`me_start`,
  email_doc.`me_end` = email_doc_bkp.`me_end`,
  email_doc.`me_total` = email_doc_bkp.`me_total`
where email_doc.`id` = email_doc_bkp.`id`;

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:rgb192
ID: 39890880
I tried the insert and insert ignore and then started again

but these are the only columns I have deleted ( I thought I deleted more columns)

`me_description`,`me_start`,`me_end`,`me_total`,

which are a description of my hours, my start time, my end time, my total time


update email_doc, email_doc_bkp set
   email_doc.`me_description` = email_doc_bkp.`me_description`, 
  email_doc.`client_description` = email_doc_bkp.`client_description`,
  email_doc.`sms_type` = email_doc_bkp.`sms_type`,
  email_doc.`client_start` = email_doc_bkp.`client_start`,
  email_doc.`client_end` = email_doc_bkp.`client_end`,
  email_doc.`client_total` = email_doc_bkp.`client_total`,
  email_doc.`me_start` = email_doc_bkp.`me_start`,
  email_doc.`me_end` = email_doc_bkp.`me_end`,
  email_doc.`me_total` = email_doc_bkp.`me_total`
where email_doc.`id` = email_doc_bkp.`id`;

Open in new window



I only want to update email_doc  
only update one table

are you sure this query will only update one table
0
 

Author Comment

by:rgb192
ID: 39902164
I only want to update email_doc  

because the query is
update email_doc, email_doc_bkp set

where two tables are in the update clause.

I can not test because I do not want to change the data in these production tables.
0
 

Author Comment

by:rgb192
ID: 39917978
how can I test because I think this command will update both tables and I do not want to alter both tables
0
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39923776
Sorry for late answer. If you read the whole query you'll see that it reads from both tables but updates only email_doc.
If unsure, why not create sample tables from both and try the query there.
0
 

Author Closing Comment

by:rgb192
ID: 39950576
yes updated one table

this is the query I used

Thank you and thanks for other suggestions.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now