How to migrate MS SQL database running on remote machine to my local MySQL database running on linux, preserving encoding

I have a remote Windows 10 machine running SQL Server (v12, Express edition) and I need to transfer one database to my machine, which is running MySQL database (MariaDB).

MS SQL database contains special characters (Czech language diacritics) that get messed up if I try to use MySQL Workbench on the remote machine to migrate database to MySQL running on the same remote machine.

  • Dynamic SQL with varchar in replace of table variable
  • ODBC ERROR Details Communication link failure with database. Trying to reconnect the link
  • Would building an application using a Sql Server Database File (mdf) be a terrible idea?
  • Size of varchar columns
  • Cannot create login in stored procedure with EXECUTE AS OWNER?
  • Add Computed Column and Put Index on it
  • How can I transfer database correctly?

    One Solution collect form web for “How to migrate MS SQL database running on remote machine to my local MySQL database running on linux, preserving encoding”

    Using MySQL Workbench, I have tried these combinations, none of them worked:

    remote MS SQL > remote MySQL native ODBC driver

    remote MS SQL > remote MySQL ODBC DataSource driver

    remote MS SQL > remote MySQL freeTDS ODBC driver (with or without UTF-8)

    virtual MS SQL > virtual MySQL native ODBC driver

    virtual MS SQL > virtual MySQL ODBC DataSource driver

    virtual MS SQL > virtual MySQL freeTDS ODBC driver (with or without UTF-8)

    In either of those cases, either migration process got stuck (Non responding Workbench) or characters were not transferred correctly.

    Total time of trial and error was around 12 hours.

    Here I am sharing with you one way how I was able to successfully migrate whole MS SQL database to my local MySQL server running on my dev machine.

    As I was connecting to the remote W10 machine via Remote Desktop Connection (RDC) from my VirtualBox machine, at first I wanted to remove this extra step to make things easier.

    1. if you do not have yet, download and install free Virtual Box machine that Microsoft offers and start it

    2. from this virtual machine start RDC and before connecting, add local disk on the virtual machine to be available on remote machine RDC dialog

    3. on remote machine start SQL Server management studio and create a backup of the database (Right click on the desired database, Tasks > Backup…, save backup file to the disk)

    4. copy this backup file to your drive on virtual machine by navigating to Networks and uploading the file to C: drive on your virtual machine

    5. install on your virtual machine MS SQL server, Express edition which is free, along with SQL Server Management Studio (I have downloaded 2014 version)

    6. create new database and select Tasks… > Restore > Database…, select the file you uploaded to your virtual machine, select Overwrite the existing database.. select Overwrite the existing database..

    7. create new login for your MS SQL server (right click on Logins), select desired login and password, assign a sysadmin role, just to make things easier, as this is a one-time migration process assign a sysadmin role

    8. on your local Host Linux machine install latest MySQL Workbench, I assume that MySQL database is running locally already, if not, install it

    9. because you’re on Linux, you’ll need extra step to install FreeTDS ODBC driver to your system, if this link, how to install FreeTDS driver on Linux goes dead, search for Database Migration Wizard / Microsoft SQL Server migration in MySQL Workbench manual

    10. setup virtual machine to be accessible from your Host linux system, I’ve done that by selecting Attached to: Bridged adapter and picking wlan0 in the settings of the virtual machine

    11. start virtual machine and select IP range from the same network as you adapter uses
      select IP range from the same network as you adapter uses

    12. on your virtual machine add a firewal rule for MS SQL server or temporarily turn firewall off

    From now on, everything is done on your local Linux machine with virtual machine running

    1. start MySQL Workbench’ Database Migration, select MS SQL as a source machine, input correct IP address that you setup in your virtual machine earlierinput correct IP address that you setup in your virtual machine earlier

    2. as a target database, use your local MySQL database settings and credentials

    3. in the Target Creation Options step select only Create a SQL script file and select Keep schemas if they already exit.

    4. go through next steps until you reach Data Transfer Setup. There select Online copy of table… but do not press Next

    5. edit the script saved in the step 15.:

    remove:

    CREATE SCHEMA IF NOT EXISTS `Test` ;
    

    add:

    CREATE DATABASE `Test` DEFAULT CHARACTER SET utf16 COLLATE utf16_czech_ci;
    

    alter all create table definitions by adding desired character set and collation to the table definition:

    CREATE TABLE IF NOT EXISTS `TestTable` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) COLLATE utf16_czech_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf16 COLLATE=utf16_czech_ci;
    
    1. run this updated script in your MySQL database

    2. go to the next step in MySQL Workbench and data should import correctly

    There may be some steps not explicitly stated, please let me know in comments if you need some clarifications

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.