April 12, 2022 at 5:47 pm
Out customers use different SQL databases, like PostgreSQL, SQL Server, MySQL. We need to import data with a schema into our MS SQL Server database each time the customer sends us their DB dump.
The problem is, that different databases uses dump files in different formats, and that makes it difficult to automate the process.
Ideally we would receive a dump in a single common format, regardless of the customer's database. This could be eventually a CSV format. But which format should be used for the database schema, so that PostgreSQL or MySQL, etc., would create a dump file with the same structure/syntax?
April 13, 2022 at 2:10 pm
But which format should be used for the database schema, so that PostgreSQL or MySQL, etc., would create a dump file with the same structure/syntax?
There is no such thing. Each of the various RDMS's had implemented and extended the ANSI SQL standards differently. Unless the databases have been written specifically using only ANSI standard code (doubtful), exporting the schema's from one and attempting to execute it on another will require changes to be made. The other issue is the datatypes. They are different across platforms.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 13, 2022 at 2:53 pm
So, you're best bet is start with a CSV file. Instead of trying to take MySQL/PostgreSQL/Oracle backups/dumps, export the data into a common format; CSV, JSON, whatever. Then, import from that common format. More work? Yes. However, as you've discovered, you can't restore backups from other database systems into SQL Server. You have to deal with data movement.
Azure Data Factory offers a whole bunch of possible solutions here. Older school, you could look to SSIS.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 13, 2022 at 5:36 pm
I'll also tell you that a lot of companies aren't going to send you a database backup because there's a whole lot of stuff in a backup that they might not want you to see including but not limited to the names of users that have some form of priv associated with the database.
CSV is usually fine... true CSV is better, and JSON is fine but tag-bloat can get ya.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply