How to migrate a database from SQL Server to MySQL

First, the migration database Schema.

First, the physical model of the SQL Server database is reversed using the reverse engineering capabilities of Sybase PowerDesigner. The specific operation is to select "File" in PowerDesigner, "Reverse Engine" then select Database, select the DBMS as SQL Server,

Then select the data source, that is, the SQL Server database server to which you want to connect, and then select the name of the database to reverse, such as select "Wss_content_80":

Click OK to generate the physical model diagram:

Then click the change current DBMS under the Database menu to modify the existing DBMS to MySQL 5.0,

Click OK to generate the physical model for MySQL and then click Generate Database under the Database menu to generate the DB script file.

The next step is to manually modify the contents of the generated script.

Replace all of the dbo.

Delete a statement such as create user.

If some characters are keywords in mysql, you must use "'" (the number 1 on the keyboard matches the one on the left) to match the box.

Add the storage engine required by MySQL, such as each build table statement followed by:

ENGINE = INNODB CHARACTER SET UTF8 COLLATE utf8_general_ci;

Run the generated script once in MySQL to create the database.

Second, the Migration data content

The data content can only be done by generating an INSERT statement.

You can generate an insert script for data in a SQL Server database by first using the Generate script feature of SSMs (right-click on the database, select the Task Build Script option).

First select the table that you want to migrate data from, here we all choose all the tables:

Then click Next and select Save script to the new query window:

Click the Advanced option to select the type of data to script in the Advanced Options window as data only:

Then "OK" the next step is to generate the Insert script file.

Modify the resulting script file, mainly with the following modifications:

Use the bulk substitution method to remove [] This is SQL Server compliant, not in MySQL.

Use the bulk substitution method to remove the dbo.

Some words are keywords in mysql, so you need to use "'".

With regard to datetime type data, it is necessary to manually modify the SQL Server to generate such statements by default, there is no way to parse in MySQL:

CAST (0x00009eef00000000 as DateTime)

Add one for each row; Represents the end of an INSERT statement. This semicolon may not be required in SQL Server, but it is required in MySQL. The simple approach is to use an advanced text editor (such as notepad++) to replace \ r \ n with; \ r \ n.

(go) How to migrate a database from SQL Server to MySQL