Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Cross platform database table backup/restore

by gwhite (Friar)
on Oct 15, 2010 at 15:16 UTC ( #865488=perlquestion: print w/ replies, xml ) Need Help??
gwhite has asked for the wisdom of the Perl Monks concerning the following question:

Going through the CPAN list, from the names I can't find any modules that backup a DB table cross platform. (if I could just support mysql, pg, oracle and mssql I could live with that). I found a couple that backup a whole database, but I just need to backup a table, make some changes and restore if something goes badly. Any suggestions?

g_White

Comment on Cross platform database table backup/restore
Re: Cross platform database table backup/restore
by MidLifeXis (Prior) on Oct 15, 2010 at 15:36 UTC

    Is the table prebuilt on the $dest DB? Any concerns with encoding? Differing data types? Large amounts of space?

    If not, why not something as simple as:

    1. start transaction on $dest
    2. Start cursor on $source (select * from source_table)
    3. delete data on $dest (delete from backup_table)
    4. insert each row from $source to $dest (get from $source, save to $dest)
    5. end transaction on $dest

    This won't be terribly fast if it is a large table. There may also be better tools for this (export to a CSV, import from CSV, for example, given the right format of the data).

    Are there any special constraints on your problem that stop you from using the straightforward solution?

    --MidLifeXis

      We are giving the client the option to merge (but not add) some data into the database

      We want to take a snapshot of the table premerge, if they discover something is awry (sometimes days and several updates later) we want the premerge data. Some of our clients have 100,000+ records in each of 5-6 tables. Encoding and such isn't an issue, we just support various DB platforms, so I was hoping there was a module that let me do something like a $dbh->backup($mytable_name, $myholding_file); & a $dbh->restore($myholding_file, $mytable_name); (and yes I know if updates have been made we may need to do more, but this would be enough 75% of the time)

      g_White

        And the export/import to/from CSV or XML or SQL would work, just looking to see if there was a cross platform tool that did this amongst some of the cryptic DBIx module names....

        g_White

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://865488]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (7)
As of 2014-07-26 07:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (175 votes), past polls