Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

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?


Comment on Cross platform database table backup/restore
Replies are listed 'Best First'.
Re: Cross platform database table backup/restore
by MidLifeXis (Monsignor) 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?


      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)


        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....


Log In?

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (7)
As of 2015-12-02 00:22 GMT
Find Nodes?
    Voting Booth?

    My keyboard shows this many letters:

    Results (31 votes), past polls