views:

12260

answers:

9

I've been banging my head against SQL Server 2005 trying to get a lot of data out. I've been given a database with nearly 300 tables in it and I need to turn this into a MySQL database. My first call was to use bcp but unfortunately it doesn't produce valid CSV - strings aren't encapsulated so you can't deal with any row that has a string with a comma in it (or whatever you use as a delimiter) and I would still have to hand write all of the create table statements as obviously CSV doesn't tell you anything about the data types.

What would be better is if there was some tool that could connect to both SQL Server and MySQL and then do a copy. You lose views, stored procedures, trigger, etc. but it isn't hard to copy a table that only uses base types from one DB to another... is it?

Does anybody know of such a tool? I don't mind how many assumptions it makes or what simplifications occur, as long as it supports integer, float, datetime and string. I have to do a lot of pruning, normalising, etc. anyway so I don't care about keeping keys, relationships or anything like that but I need the initial set of data in fast!

+3  A: 

I've heard a few people using MSSQL2MySQL with success, but I can't vouch for it myself.

Greg Hurlman
I tried this for my work site and I wasn't able to get it to work. It was a fairly simplistic database so I'm not sure why it didn't work.
jtyost2
A: 

Rolling your own PHP solution will certainly work though I'm not sure if there is a good way to automatically duplicate the schema from one DB to the other (maybe this was your question).

If you are just copying data, and/or you need custom code anyway to convert between modified schemas between the two DB's, I would recommend using PHP 5.2+ and the PDO libraries. You'll be able to connect using PDO ODBC (and use MSSQL drivers). I had a lot of problems getting large text fields and multi-byte characters from MSSQL into PHP using other libraries.

pix0r
A: 

Another tool to try would be the SQLMaestro suite - http://www.sqlmaestro.com It is a little tricky nailing down the precise tool, but they have a variety of tools, both free and for purchase that handle a wide variety of tasks for multiple database platforms. I'd suggest trying the Data Wizard tool first for MySQL, since I believe that will have the proper "import" tool you need.

Dillie-O
A: 

@Greg: Its old, very rough and has trouble with all sorts of things... but it works :)

41 of my tables won't import on a first pass but I think that switching autonumber columns to just plain integer will solve most of that - the other 200+ are copying over quite speedily so its already been worth the download.

Mat
+11  A: 

The best way that I have found is the MySQL Migration Toolkit provided by MySQL. I have used it successfully for some large migration projects.

Nick Berardi
+1 I used that too and it worked well.
Marcel Tjandraatmadja
+1 - It is now EOL, and MySQL Workbench is going to include a migration plugin (past version 5.2) but for now use the following link: http://dev.mysql.com/downloads/gui-tools/5.0.html
MarkRobinson
doesn't want to convert DATE or DATETIME columns :(
Robert Ivanc
A: 

Did you have any luck with MSSQL2MySQL?

Marcel Tjandraatmadja
+1  A: 

SQL Server 2005 "Standard", "Developer" and "Enterprise" editions have SSIS, which replaced DTS from Sql server 2000. SSIS has a built in connection to it's own DB, and you can find a connection that someone else has written for MySQL. Here is one example. Once you have your connections, you should be able to create an SSIS package that moves data between the two.

I have not had to move data from SQlServer to MySQL, but I imagine that once the MySQl connection is installed, it works the same as moving data between two SQLServer DBs, which is pretty straight forward.

Sean
+1  A: 

Using MSSQL Management Studio i've transitioned tables with the MySQL OLE DB. Right click on your database and go to "Tasks->Export Data" from there you can specify a MsSQL OLE DB source, the MySQL OLE DB source and create the column mappings between the two data sources.

You'll most likely want to setup the database and tables in advance on the MySQL destination (the export will want to create the tables automatically, but this often results in failure). You can quickly create the tables in MySQL using the "Tasks->Generate Scripts" by right clicking on the database. Once your creation scripts are generated you'll need to step through and search/replace for keywords and types that exist in MSSQL to MYSQL.

Of course you could also backup the database like normal and find a utility which will restore the MSSQL backup on MYSQL. I'm not sure if one exists however.