SQL Server 2000 export database to csv

Question

I need to move an SQL Server 2000 database to MySQL. I'm running into several complications. I'm no expert on SQL Server. To get going I'd like to have a csv-dump of structure and data.

First issue, 3 tables which are clearly in use by the application are not listed in the Tables view. When generating a diagram, a popup appears stating that those tables couldn't be loaded. The weird thing is that data from those tables is actually returned in the application (not cache).

The second issue is the use of DTS. I'm using the wizard to export the DB to text. I'm selecting "Copy table(s) and view(s) from the source database". Then, I get to choose the file type, delimiters etc. There's a drop-down which has the first table selected. My problem is, I can only select 1 table but I need to export the entire DB. When continuing, the server will only export the first table. How do I get it to export the entire DB? Doing it table by table will take far too much time.

Answer 1

There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...).

Look at this question over at DBA.SE for a number of references to migration tools. Also, the MySQL website publishes a white paper on MS SQL migration which has some useful information about how data types and functions could be substituted and describes how MySQL Workbench could be used for migration.

While DTS could be used to perform a one-time migration, it probably is not worth the effort - it has been created with repeatable procedures in mind (i.e. database synchronization, repeated export tasks etc.). The amount of time for creating a transformation package is going to be far higher than what you are aiming for.