How to migrate from MySQL to SQL Server 2008

I have MySQL database backup file (Mysql version 5.1) and i want to restore it in Sqlserver 2008 how do i restore it?

Solution 1:

You can't restore a MySQL backup into SQLServer 2008 automatically. You can write a conversion script to convert the data, or use a tool like Microsoft DTS (called SQL Server Integration Services nowadays).

Solution 2:

If you still have access to the database in MySQL, you should be able to get the data out of MySQL into a format usable my MS SQL by using the mysqldump command and the --compatible=mssql command line switch. Although, you do sometimes end up having to edit the dump file manually if it gives you errors importing it into SQL Server.

If you only have access to the backup file, the same tactic may work if you import it into a new instance of MySQL and then dump it to a new backup file.

Solution 3:

Use a SQL script generator on your MYSQL database and run the script file on your MSSQL database. As long as you don't have overlapping different types you should be good.

To create a database create script:

1. Open the MySQL Query Browser window
2. Open a new Script tab (Ctrl + Shift + T)
3. Drag the required tables onto the script tab
4. You should see the generated CREATE TABLE script

Then you need export the data to CSV so your can import it.

Additionally Redgate has an early release of SQL Compare for MYSQL you could play with to see if it can help generate the scripts between the two types ( i have no idea, as i havent tried it yet ):

http://www.red-gate.com/products/MySQL_Compare/index.htm