How to transfer an MS SQL database running on a remote computer to my local MySQL database running on Linux while preserving the encoding

I have a remote computer running Windows 10 with SQL Server (version 12, version Express), and I need to transfer one database to my machine running the MySQL database (MariaDB).

The MS SQL database contains special characters (Czech diacritics) that are messed up if I try to use MySQL Workbench on a remote computer to transfer the database to MySQL running on the same remote computer.

How can I transfer the database correctly?

0
mysql sql-server mysql-workbench database-migration
source share
1 answer

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

MS SQL Remote> MySQL ODBC Remote Source 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 external MySQL ODBC driver

virtual MS SQL> MySQL ODBC DataSource virtual driver

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

In any of these cases, either the migration process is stuck (Non responseing Workbench) or the characters were not correctly transmitted.

The total trial and error time was about 12 hours.

Here I shared with you one of the ways I was able to successfully transfer the entire MS SQL database to my local MySQL server running on my dev machine.

When I connected to a remote W10 computer through a Remote Desktop Connection (RDC) from my VirtualBox machine, I first wanted to remove this extra step to simplify the task.

  • if you haven’t already done so, download and install the free Microsoft Box offered by Microsoft and run it

  • from this virtual machine, start RDC and before connecting add the local disk to the virtual machine, which will be available on the remote computer RDC dialogue

  • on a remote computer, start SQL Server Management Studio and create a backup of the database (right-click on the database you need, tasks> backup ..., save the backup file to disk)

  • copy this backup file to your disk in the virtual machine by going to the Network and uploading the file to the C: drive on your virtual machine.

  • install on your virtual computer MS SQL Express Edition, which is free, along with SQL Server Management Studio (I downloaded version 2014)

  • create a new database and select "Tasks ..."> "Restore"> "Database" ..., select the file that you downloaded to your virtual machine, select "Overwrite existing database". select Overwrite existing database ..

  • create a new login for your MS SQL server (right-click on logins), select the desired login and password, assign the sysadmin role to simplify the process, as this is a one-time migration process assign sysadmin role

  • on your local machine with host Linux, install the latest version of MySQL Workbench, I assume that the MySQL database is running locally already, if not, install it

  • since you are on Linux, you will need an additional step to install the FreeTDS ODBC driver on your system, if this link , how to install the FreeTDS driver on Linux does not work, search the Microsoft SQL Server Database Migration / Migration Wizard in the MySQL manual Workbench

  • configure the virtual machine to be accessible from your Linux host system, I did this by selecting Attached to: Bridged adapter and selecting wlan0 in the virtual machine settings

  • start the virtual machine and select the IP range from the same network as your adapter. select an IP range from the same network as your adapter using

  • on your virtual machine add a firewal rule for the MS SQL server or temporarily disable the firewall

From now on, everything is done on your local Linux computer using a virtual machine

  1. run MySQL Workbench 'Database Migration, select MS SQL as the source computer, enter the correct IP address that you set earlier on your virtual machine <img src = "https://i.stack.imgur.com/qiIPb.png" alt = "enter the correct IP address that you set earlier on your virtual machine">

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

  3. in the "Target creation options" step, select only Create SQL script file and select "Save schemas" if they are already completed.

  4. Follow these steps until you reach the data transfer setting. There select Online copy table ... but do not click Next

  5. edit the script saved in step 15 .:

delete:

CREATE SCHEMA IF NOT EXISTS `Test` ; 

add:

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

Modify all table creation definitions by adding the desired character set and mapping 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 the data should be imported correctly

There may be some steps not explicitly indicated, please let me know in the comments if you need some clarification

0
source share

All Articles