I have received a dump from mssql database from which I had to extract the data and put it in some format that I'm more familiar with. In this case MySQL and later to CSV. My main OS is Ubuntu and that have an impact on decisions made throughout this article. I hope this will help someone when faced with a similar task.
Few answers on StackOverflow suggested using different tools - one of them is MySQL workbench as it has the option to migrate between two databases, where destination DB is MySQL.
Running Microsoft SQL Server with Docker
This will be a source from which Workbench will pull the data from. Because it was a Microsoft product I worried about how to do the whole thing on OS other than windows. Luckily there is a docker image with a MySQL server which made things so much easier. Destination will be a docker image as well.
These are instructions on how to set up "source" data which include:
- starting a docker container
- restoring MySQL dump
Based on this guide we will restore data from a dump.
For data to be available for restore you have to put it in the directory which will be available to the container too. So create a new directory, cd into it and paste MySQL dump file.
From the same folder start the container.
~❯ docker run --name mssql_test --rm -e 'ACCEPT_EULA=Y' -v $(pwd):/var/opt/mssql/backup -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-CU8-ubuntu
Confirm that File is Available (optional)
Open the bash.
docker exec -it mssql_test bash
cd to /var/opt/mssql/backup, ls should list the backup file there.
Type exit to restore to the host system.
Restore the Backup
Now we will launch sqlcmd and restore the database:
docker exec -it mssql_test /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password'
Type the following commands. And as mentioned in the original article you cannot copy and paste the whole command and you have to do it line by line.
To check what files does backup contain type the following:
RESTORE FILELISTONLY
FROM DISK = '/var/opt/mssql/backup/the_ratings.bak'
GO
This will print a messy output but important thing is to get the LogicalName from it which will be used in the next command. For example, I got two records, one was dbh-ratings and second dbh-ratings_log so by following the template from original article this is what it should be typed:
RESTORE DATABASE the_ratings
FROM DISK = '/var/opt/mssql/backup/the_ratings.bak'
WITH MOVE 'dbh-ratings' TO '/var/opt/mssql/data/the_ratings.mdf',
MOVE 'dbh-ratings_log' TO '/var/opt/mssql/data/the_ratings_Log.ldf'
GO
After some time you should get output similar to this:
RESTORE DATABASE successfully processed 30852 pages in 3.183 seconds (75.722 MB/sec).
You can exit the container by pressing Ctrl + C.
For any problems consult the original article.
Run Destination MySQL Database
Start the container
~❯ docker run --name mysqltest --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql
This will be the destination database to which we will copy data. Note that it has --rm flag which means that container will be destroyed when you stop it.
Transfering data with MySQL Workbench
Note: If you just want to explore data, I suggest checking this great tool - DBeaver, which is capable of connecting to Microsoft SQL Server. It will automatically download any needed connection driver.
use host: localhost
username: su
password: yourStrong(!)Password
Configuration and Drivers
This should be easy, unfortunately, Workbench needs to be configured first to use them. If you get errors similar to this
Could not connect to Source DBMS. [00000] [iODBC][Driver Manager]{FreeTDS}: cannot open shared object file: No such file or directory (0) (SQLDriverConnect)
it means that workbench is missing iODBC drivers. Those have to be compiled from source, and instructions to that are here. Please follow the instructions there. After build_freetds.sh the command output should be something like this:
Go to /tmp/freetdsbuild/freetds-dev.1.1.57 and type make install as the root user
After that, install the driver which will be installed as /usr/local/lib/libtdsodbc.so
Now when driver was built, you have to configure Workbench to use it. Check instructions here. Watch for Driver string - should be same as in the "Open ODBC Administration" settings. If you named your driver Workbench FreeTDS Do the same while configuring the connection later.
Running a Migration
Go to Database > Migration Wizard and follow the steps. Everything should be clear so far.
Source connection will be of Microsoft SQL Server type, connection method ODBC (FreeTDS), Driver should be the same as used when the driver was configured (FreeTDS or Workbench FreeTDS). Password is yourStrong(!)Password. Other parameters can be a default.
Target connection will be localhost with a password root.
Finish the wizard and data will be moved to MySQL.
Migration Challenges
The migration tool is not perfect and it will generate incorrect tables. In my particular case, I had problems with unique indexes. So in one of the steps, I had to change them to normal indexes to remove any constraints.
To make the process easier, import only the tables you really need.
The issue with encoding can be solved by making the destination table column of type BLOB and then handle encoding at reading time. For example something like this:
SELECT *, CAST(Kommentar AS CHAR(10000) CHARACTER SET utf8) as Kommenter FROM dbo.Reviews order by Datum desc;
From here I had all the data in the database system that I'm familiar with and I can explore data with MySQL Workbench, query it, make a database dump or make a script that connects to the database.