Universal Database Backup / Extract Format

views:

answers:

Universal Database Backup / Extract Format

Hello,

does there exist a universal database backup/extract format?

I'll explain where I'm coming from: our application supports several database vendors, DB2, MsSql, MySql and Oracle. Currently when we request a backup from a customer they must make a full backup in the vendor specific format.

We have a small but powerful homegrown utility which allows us to import and export fully or partially from any database vendor. Unfortunately the common extract format is dBase tables which does not work very well as we are now using SQL field types that are not supported.

The solution is to use an existing industry standard backup/extract format, if one exists. While I don't mind inventing my own format an industry standard format would have more possiblities.

Does one exist or will I have to invent one?

Thanks in advance,

Stephen.

How about XML? CSV?

Jay Riggs 2009-10-07 18:34:48

I would have to decide on the format in this case, so I would consider that a homegrown format. Also, as below, what about compression.

Steve 2009-10-07 18:56:35

@Steve: You asked about industry standards for doing this. XML and CSV are it.

Ben S 2009-10-07 20:16:36

Thats pretty much as I understood it. So even though XML and CSV are industry standards there is no industry standard to describe how you might store a full database backup in it. If I used XML I would have to decide on how to organise the data within the XML file rather than a standard way. What I would really like is a situation whereby I have a backup file (backup.xyz) which I can import/restore into any database in a similar way that MsSQl can restore a .bak file

Steve 2009-10-09 05:54:12

All the DBMS I know of (including all the ones you've listed) support exporting and importing to CSV.

For bonus points, compress the exports to save space.

Ben S 2009-10-07 18:36:29

Compression is definitely a must, if only to have the option.

Steve 2009-10-07 19:02:13

The only universal export format is some variation on the theme of a text dump.

There are multiple possible formats - fixed or delimited (or separated). When delimited, it can be CSV or some other related format. Escape conventions, especially for embedded newlines, tend to be idiosyncratic.

Another tricky part is how the different DBMS prefer to encode things like binary data BLOB values - Base64, hex, ...and probably some others...

XML is also a possibility, but there is no standardization on which XML schema or DTD to use.

Jonathan Leffler 2009-10-07 18:46:15

I understand the difficulty with non-standard field types. Luckily, we only use a common subset, for the moment. XML would appear to be the ideal choice until you consider that some of the databases exceed 20 Gb. XML would add a lot of overhead unless compression is used and event then....?

Steve 2009-10-07 19:00:57

I think you have to look for a tool who can pump data.

One example here : http://www.clevercomponents.com/products/datapump/ibdatapump.asp

It will not specifically fit your need but may be it is the way to go.

Hugues Van Landeghem 2009-10-07 21:46:57

I downloaded it and tried to run it, but unfortunately it does not run without Interbase installed. However, it would appear that IBPump copies data directly from the source to the destination without passing through an intermediary format. We would need an intermediary format as it is rare that the source and destination databases are on the same network. Sometimes the customer databases are so big that an external HD and a courrier is required as an over the internet transfer would take too long.

Steve 2009-10-08 06:04:45

ansaurus

tags:

views:

answers:

Universal Database Backup / Extract Format

related questions