Migrating data from MySQL to SQL Server

Question

I am looking to migrate data from MySQL to SQL Server. MY first idea is to take data backup in .bak format and dump into the SQL Server. So is there any way to get a MySQL database backup file in .bak format?

Else, is there any simpler, easier way to migrate data from MySQL to SQL Server?

.bak is a proprietary binary format specific to SQL server. MySQL can only dump to text-based files, which have the raw SQL statements to rebuild the db/tables/data. You can load that dump file into sql server's consoles and just run it as a long series of commands. — Marc B
– Marc B, Commented May 5, 2012 at 5:00

YvesR · Accepted Answer · 2012-05-07 17:11:30Z

1

Did you try SSIS from MS SQL (shipped with Standard or higher version)? I don't know what you want to transfer, but tables and views are easy this way:

Create a new MS SQL database (empty)
Right click on the database and select task -> import data
As source you select mySQL driver (Need to install before - http://dev.mysql.com/downloads/connector/odbc/)
Choose the DB, connect to it, then follow the assistant to select views and tables
At the end of the assistant you can save the SSIS package and edit it with a SSIS project in Visual Studio Business Intelligence (shipped also with MS SQL standard or higher) OR you just run it. If you run into transfer/convert problem you might need to edit it with the Studio version and modify the package to handle the tables/columns with problems.

answered May 7, 2012 at 17:11

YvesR

6,2829 gold badges46 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rory Hunter · Accepted Answer · 2012-05-07 17:05:48Z

0

I hope you don't have to do this, but as a last-resort you can generate a custom SQL data dump as follows;

SELECT CONCAT_WS( ' '
   , 'INSERT INTO [dbo].[table_name] (col1,col2) VALUES'
   , col1
   , CONCAT( '"', col2, '"' )
   , ';'
) AS '-- Inserts'
FROM table_name
ORDER BY col1 ASC;

This can be as complex as you like, though I'd only recommend it if you experience problems getting your data into SQL Server due to e.g. automatic data conversion issues. Unless your data is massive, you'd probably be better off using mysqldump and filtering the output through e.g. sed or perl.

answered May 7, 2012 at 17:05

Rory Hunter

3,4901 gold badge16 silver badges16 bronze badges

Collectives™ on Stack Overflow

Migrating data from MySQL to SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related