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.
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.
if you do not have yet, download and install free Virtual Box machine that Microsoft offers and start it
from this virtual machine start RDC and before connecting, add local disk on the virtual machine to be available on remote machine
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)
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
install on your virtual machine MS SQL server, Express edition which is free, along with SQL Server Management Studio (I have downloaded 2014 version)
create new database and select Tasks… > Restore > Database…, select the file you uploaded to your virtual machine, select Overwrite the existing database..
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
on your local Host Linux machine install latest MySQL Workbench, I assume that MySQL database is running locally already, if not, install it
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
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
start virtual machine and select IP range from the same network as you adapter uses
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
start MySQL Workbench’ Database Migration, select MS SQL as a source machine, input correct IP address that you setup in your virtual machine earlier
as a target database, use your local MySQL database settings and credentials
in the Target Creation Options step select only Create a SQL script file and select Keep schemas if they already exit.
go through next steps until you reach Data Transfer Setup. There select Online copy of table… but do not press Next
edit the script saved in the step 15.:
CREATE SCHEMA IF NOT EXISTS `Test` ;
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;
run this updated script in your MySQL database
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