0

I have a 19 GB .sql file which is a dump of a MySQL database.

How could I go about mounting that to MS SQL server? Is it just a matter of loading the 19 GB file into management studio and hitting F5?

I don't have access to the original MySQL databases or the server they were running on.

Thank you.

Jack Marchetti
  • 15,536
  • 14
  • 81
  • 117
  • 1
    assuming your mysql stuff isn't using any mysql-specific features, such as group_concat() and the like, then yes, you should be able to just load up the .sql file, wait a **LONG** time for it to load, then run it. – Marc B Oct 21 '13 at 21:45
  • So if there are mysql-specific commands, do I just need to rewrite on the fly? Is there a tool which does this? – Jack Marchetti Oct 21 '13 at 21:55
  • you'd be better of pre-processing the dump file to make it more generic, rather than on-the-fly. 19gig is a pretty big dump file, and if something pukes part-way through, that's a LOT of server time you've wasted. – Marc B Oct 21 '13 at 21:57
  • How would I pre-process it? – Jack Marchetti Oct 21 '13 at 21:59

1 Answers1

2

I would go about it like this.

  1. Restore the MySQL databases to a MySQL server.
  2. Setup a linked server from MS SQL to MySQL
  3. Do a SELECT * INTO destinationtable FROM linkedserver.dbo.sourcetable

The only problem here is that you will need to make sure that index definitions etc are recreated.

Youre MySQL backup file will not run in MSSQL without a LOT of work.

Namphibian
  • 12,046
  • 7
  • 46
  • 76
  • Thanks man. I'll give that a shot. I think the .SQL file I have was corrupted anyway, I can't open it in Wordpad or SQL Server. I get this error: Error HRESULT E_FAIL has been returned from a call to a COM component. – Jack Marchetti Oct 21 '13 at 23:19
  • Shout if you need more information you can for example query the INFORMATION_SCHEMA.TABLES on MySQL and use it to script the SELECT INSERT statements. See this link for more information http://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation/10860122#10860122 – Namphibian Oct 22 '13 at 00:35
  • 1
    I would also add that once you get it into mySQL you can inspect it to see if there are columns or perhaps whole tables that you do not need to bring over. You might also consider breaking the task down into smaller modules: identify tables (if any) that are related through foreign keys and import them together. If all goes well move on to more tables. This reduces the likelihood that you will have to start over after a very long failed operation. – AllInOne Oct 22 '13 at 01:39