Convert sql server to mysql script

Top 3 tools for converting MS SQL Server database to MySQL - 2021

By: Hassan Shareef,
Database Administrator
Since 13 years

Table of Contents Show

Top 3 tools for converting MS SQL Server database to MySQL - 2021
1 Data Loader
2 MySQL Workbench
3 MSSQL Import & Export Tool
How do I migrate from SQL Server to MySQL?
How do I save a SQL Server database as a script?
How do I run a SQL script from MySQL command line in Windows?
How do I run a script in MySQL?

There are some situations where there is a need to migrate databases from MS SQL Server to MySQL. This could be due to several reasons. Like for example, your company has website which uses MySQL in the backend and they have MSSQL for their in-house applications. Moving data from MSSQL to MySQL is a frequent necessity that needs to be performed by DBA's.

MySQL / MariaDB is the world's most prominent open source database. Its simple to utilize and in the meantime gives performance, dependability and versatility. MySQL / MariaDB is the primary choice database for Web-based applications. It's utilized by web monsters like Facebook, Twitter, YouTube, Yahoo!, Wikipedia and a huge number of average sized organizations.

On of the significant advantage admitted by clients in reviews is it's minimal effort. The cost of running and keeping up MySQL / MariaDB is quantum of degrees less as compare to alternate databases.

Cross database migration is a complex process and it needs to be performed with proper planning and schedule. I have chosen the sample Northwind database which is given by Microsoft MSSQL as a demo database and tried to convert it to MySQL using the following tools.

Here are the tools which I have tested in the order of their efficiency and performance

1 Data Loader

It stands apart so far from all other tools. Faced the least amount of issues while converting from SQL Server to MySQL. There were only 2 issues related to default values which needed manual attention otherwise the conversion ran smoothly. The best thing which I liked was the ability to load into existing tables also. None of the other tools lets you load data into existing tables, they only create fresh tables whereas Data Loader can load data into fresh tables as well as lets you load into existing tables and even it allows column mappings. The other good thing is it lets you Synchronize source and target tables by defining your comparison columns or you can take the already chosen default Primary key columns. It also comes with it's own scheduler.

The other best feature I liked is it also comes with a separate command line tool to execute saved sessions either from other applications or you can also use Windows Task Scheduler to execute saved Sessions at particular intervals.

2 MySQL Workbench

It's the free tool from MySQL stable. It allows you to convert from MS SQL to MySQL using ODBC drivers. You should have SQL Server ODBC driver installed in order to use this tool. Once you installed the ODBC driver you will need to create ODBC DSN by clicking on ODBC Administrator in Windows Control panel. Once DSN is created you can call it from Workbench to do the conversion.

MySQL Workbench is a GUI tool for managing and working with MySQL databases. The Migration Wizard is just a part of this GUI tool.

To get to the Migration Wizard you will need to :

Start MySQL Workbench
Click on Database menu
Click on Migration Wizard as shown in the picture below

Although it migrated the tables, it failed to convert the Views. Another thing which was lacking is it doesn't let you load the data into existing tables leave alone column mapping. It also doesn't let you save the migration jobs. No synchronization support.

3 MSSQL Import & Export Tool

The MS SQL Server comes with it's own Export and Import tool. It also comes free with Express editions but in Express editions it doesn't let you save migration jobs.

It utilizes ODBC Drivers for conversion. We have to create ODBC Data Source by going into Control Panel first. If you are running the Import & Export tool in the same machine where MSSQL database is running then there is no need to download and install the ODBC driver as this driver is automatically installed when you install MS SQL Server.

You also need to install MySQL ODBC Driver. If you don't have it installed in your system, then you can download it from MySQL website.

After installation of ODBC drivers start ODBC administrator and create Data sources. To start ODBC Administrator

1 Click on Start Menu in Windows

2. Type Data Sources in Run command text box as shown below

3 Click on Data Sources (ODBC) and you will get a window as shown below

4 Click on User DSN or System DSN tab and click Add button. Then it will prompt you to choose DSN (Data Source Name), ODBC driver details and etc.

How do I migrate from SQL Server to MySQL?

Method 2: Manually Convert SQL Server to MySQL.

Step 1: Configure the ODBC Driver to Convert SQL Server to MySQL. Before using the MySQL Workbench, you have to set up the ODBC Driver to convert SQL Server to MySQL. ... .

Step 2: Use the MySQL Migration Wizard to Convert SQL Server to MySQL..

How do I save a SQL Server database as a script?

In the SQL Server Management Studio, expand Databases, and then locate the database that you want to script. Right-click the database, point to Script Database As, then point to CREATE To, and then select File. Enter a file name, and then select Save.

How do I run a SQL script from MySQL command line in Windows?

use the MySQL command line client: mysql -h hostname -u user database < path/to/test. sql. Install the MySQL GUI tools and open your SQL file, then execute it. Use phpmysql if the database is available via your webserver.

How do I run a script in MySQL?

To run SQL script in MySQL, use the MySQL workbench. First, you need to open MySQL workbench. Now, File -> Open SQL Script to open the SQL script. Note − Press OK button twice to connect with MySQL.