DBMS To Load multiple types of dump files

All we need is an easy explanation of the problem, so here it is.

I just want to know if there’s a DBMS for Linux that I can use to restore dump files from various other DBMS’s, for example MySQL, PostgresSQL, SQL Server, etc.

I am in the task of migrating data from various DBMS’s and I’m wondering if instead of using Wine for installing MSSQL, I can just install one DB to load/restore dump files and view its data and probably proceed with migration as well?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

  1. Do not use Wine to run SQL Server! SQL Server runs on Linux now – so
    if it’s not a production instance, maybe you can use the developer
    edition (caveat: IANAL – check Microsoft’s licencing for your case)

  2. Since you’re on Linux, you can use tools like sed, awk and grep
    which can be quite powerful for this sort of operation.

  3. There are any number of RDBMS-X_2_RDBMS-Y scripts out there. A quick Google of migrate from mysql to postgresql gives, for example, this page, where there is a plethora of tools both commercial and Open Source.

Finally, a big word of warning. My experience with doing this sort of task has shown me that none of the tools are perfect (far from it!). Apart from differences even in basic SQL datatypes (some custom and/or user defined) and syntax (even for relatively simple statements), you then have the insurmountable problem of “translating” STORED PROCEDUREs, TRIGGERs and FUNCTIONs, where you really are off the reservation when it comes to tools!

Given the current state of technology, it is just not possible to just put the code from one server through a script and have the code for another drop out the end! There is an attempt to harmonise these through an ISO standard called Persistent Stored Modules. It is my belief that currently, vendors have too much invested in their own languages and approaches to want to (or be forced to) change anytime soon (think pension! 🙂 )!

The only way to do this is to have a programmer (skill level depending on the magnitude and difficulty of the migration/translation) do this “by hand” – i.e. building scripts that can cope with DDL and DML. In my experience, it is relatively easy to use sed and/or awk for this work and construct a pipeline of mini-scripts like, for example:

sed -i 's/NUMBER/DECIMAL/g' my_dump_file.sql

and for that to be satisfactory.

In order to do the STORED PROCEDUREs, TRIGGERs and FUNCTIONs, you’ll need a competent, experienced programmer who knows both systems reasonably well.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply