Import a MySQL 5 database into SQL Server 2005

*Update* 8-28-2009

After trying this out, I’ve found that this does not work… at all. MSSQL compatibility mode must of been built back before 2000. I will continue searching the internet for a solution, but my feeling is that I will have manually create the tables and then add the insert statements into the database.

This is how I imported a database using MySQL 5 running on Ubuntu 8 LTS to MS SQL Server 2005.

First export the database by typing in a Ubuntu shell:

‘mysqldump -p –compatible=mssql databasename > mssql.sql’

The ‘-p’ switch prompts for a password, while the ‘mssql.sql’ file is the exported database file. If you need to specify a username, add ‘-u username’ as the first parameter or before the -p switch.

Transfer the file over to the Windows machine and  create a database for the import. Make sure you have permissions to transfer data via Windows Authentication. If you don’t have permissions, you can specify credentials using the -U and -P switches.

Run the following command line:

“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe” -S localhost -d databasename -i “mssql.sql”

Before running, verify that the ‘sqlcmd.exe’ file path is correct. The -S switch is the host name, server name, or location. You may have to use the machine name depending on the name of your instance. The -d switch specifies the database to import the data.

Leave a Reply