How to export SQL Server database to MySQL

Question

I m trying to convert from a SQL Server database backup file   bak  to MySQL  This question and answers have been very useful  and I have successfully imported the database  but am now stuck on exporting to MySQL   The MySQL Migration Toolkit was suggested  but seems to have been replaced by the MySQL Workbench  Is it possible to use the MySQL Workbench to migrate from SQL Server in the same way that the migration tool worked   Or is the Migration Toolkit still available somewhere

User · Answer

I had some data I had to get from mssql into mysql, had difficulty finding a solution. So what I did in the end (a bit of a long winded way to do it, but as a last resort it works) was:

Open the mssql database in sql server management studio express (I used 2005)
Open each table in turn and
Click the top left corner box to select whole table:
Copy data to clipboard (ctrl + v)
Open ms excel
Paste data from clipboard
Save excel file as .csv
Repeat the above for each table
You should now be able to import the data into mysql

Hope this helps

User · Answer

downloads are no more available on the official website  http   dev mysql com downloads gui-tools 5 0 html  instead  take a look here  http   download softagency net MySQL Downloads MySQLGUITools

User · Answer

You can do this easily by using Data Loader tool  I have already done this before using this tool and found it good

User · Answer

I use sqlyog to migrate from mssql to mysql  I tried Migration toolkit and workbench but liked sqlyog for its SJA  I could schedule the import process and could do incremental import using WHERE clause

User · Answer

You can use MySQL Workbench which provides a way to quickly migrate data and applications from Microsoft SQL Server to MySQL employing less time and effort   This tool has a lot of cool features like      Database migrations - enables migrations from Microsoft SQL Server  Sybase ASE and PostgreSQL  Migration project management - allows migrations to be configured  copied  edited  executed and scheduled    Read more http   www mysql com products workbench migrate

User · Answer

if you have a MSSQL compatible SQL dump you can convert it to MySQL queries one by one using this online tool  http   burrist com mstomy php  Hope it saved your time

User · Answer

It looks like you correct   The Migration Toolkit is due to be integrated with MySQL Workbench - but I do not think this has been completed yet   See the End-of-life announcement for MySQL GUI Tools  which included the Migration Toolkit    http   www mysql com support eol-notice html  MySQL maintain archives of the MySQL GUI Tools packages   http   dev mysql com downloads gui-tools 5 0 html

User · Answer

As mentioned above  if your data contains tab characters  commas  or newlines in your data then it s going to be very hard to export and import it with CSV   Values will overflow out of the fields and you will get errors   This problem is even worse if any of your long fields contain multi-line text with newline characters in them   My method in these cases is to use the BCP command-line utility to export the data from SQL server  then use LOAD DATA INFILE    INTO TABLE command in MySQL to read the data file back in   BCP is one of the oldest SQL Server command line utilities  dating back to the birth of SQL server - v6 5  but it is still around and still one of the easiest and most reliable ways to get data out   To use this technique you need to create each destination table with the same or equivalent schema in MySQL   I do that by right clicking the Database in SQL enterprise manager  then Tasks- Generate Scripts    and create a SQL script for all the tables   You must then convert the script to MySQL compatible SQL by hand  definitely the worst part of the job  and finally run the CREATE TABLE commands on the MySQL database so you have matching tables to the SQL server versions column-wise  empty and ready for data      Then  export the data from the MS-SQL side as follows    bcp DatabaseName  TableName out TableName dat -q -c -T -S ServerName -r  0 -t   t     If you re using SQL Server Express  use a -S value like so   -S  ComputerName SQLExpress    That will create a file named TableName dat  with fields delimited by   tab   and rows delimited by  0 NUL characters   Now copy the  dat files into  tmp on the MySQL server and load on the MySQL side like so   LOAD DATA INFILE   tmp TableName dat  INTO TABLE TableName FIELDS TERMINATED BY    t   LINES TERMINATED BY   0     Don t forget that the tables  TableName in this example  must be created already on the MySQL side   This procedure is painfully manual when it comes to converting the SQL schema over  however it works for even the most difficult of data and because it uses flat files you never need to persuade SQL Server to talk to MySQL  or vice versa

User · Answer

PhpMyAdmin has a Import wizard that lets you import a MSSQL file type too   See http   dev mysql com doc refman 5 1 en sql-mode html for the types of DB scripts it supports

User · Answer

I used the below connection string on the Advanced tab of MySQL Migration Tool Kit to connect to SQL Server 2008 instance   jdbc jtds sqlserver    sql server ip address  1433  lt db name gt  Instance  lt sqlserver instanceName gt  user sa password PASSWORD namedPipe true charset utf-8 domain     Usually the parameter has  systemName instanceName   But in the above  do not add  systemName    use only InstanceName     To check what the instanceName should be  go to services msc and check the DisplayName of  the MSSQL instance  It shows similar to MSSQL instanceName   Hope this help in MSSQL connectivity from mysql migration toolKit

User · Answer

MySQL Migration Toolkit  1 1 10  still available for download from here   http   downloads mysql com archives php p MySQLDeveloperSuite amp v 1 1 10

[mysql] How to export SQL Server database to MySQL?

Examples related to mysql

Examples related to sql-server

Examples related to database-migration