[Resolved] Create MySQL database from MS SQL Server backup without ODBC
I have had to migrate a SQL Server database to MySQL, having only the SQL Server database backup file, and I would like to post the steps here.
The SQL Server database backup file is a binary file, not a SQL script. It can only be read by SQL Server. So the migration covers two phases:
1 - Creating a new SQL Server database from the backup file.
2 - Migrating the SQL Server database to MySQL.
Phase 1 - Creating a new SQL Server database from the backup file
You need to install SQL Server in order to be able to read the SQL Server database backup file.
SQL Server only can be installed under Windows, so you need to have Windows. If you are working with any other operating system, you can use VirtualBox to create a virtual environment to install Windows in your operating system, just as if it were a piece of software more.
It is important to take into account the versions of Windows and SQL Server, as not every ones are compatible, and no error message is shown. For instance, SQL Server 2005 Enterprise Edition is not compatible with Windows XP, no matter how many service packs it has. However, Windows XP is compatible with all other SQL Server editions (Developer, Standard, Express, etc.).
Other hardware and software must be considered. Look the SQL Server screen books. Specially relevant is the IIS Windows component. Make sure it is installed, before you proceed with the SQL Server installation. It is required to create a database connection.
In the installation process, the most important thing to know is that it cannot be any error or warning in the initial requirement list. After that,
it will be asked which components do you want to install. At this point, if you see some components disabled, the SQL Server version may not be compatible with the Windows version.
The installation may take about 20 minutes.
Once you have installed SQL Server, open the SQL Server Management Studio. In the server connection dialog, in the server name box type localhost if the server is the same machine where SQL Server is, or the name of the machine where the server is. Remember: IIS must be installed and started (it can be started in the services panel).
When the server connection is established, in the left panel, in the object explorer, under the server name, right-click on Databases, and click on Restore database.... The Restore database dialog is displayed.
In the left menu can be seen two sections: General and Options. Initially you are in the General one. There, in the To database box you have to specify the name of the database you are about to create. Check From device, and select the SQL Server backup file you have. Add it and check it in the source list.
Then switch to the Options section, with the left menu. There you may select the restoring options you consider. The main point here is the database paths, in the Restore as column of the database files table. By default these paths are the same ones as in the backup file, which may have been created in another computer, and might not make sense in your computer. So, specify the paths you want to store the database files on.
Now click on OK, and if everything when ok, you have your new SQL Server database from the backup file :)
Phase 2 - Migrating the SQL Server database to MySQL
I have found just one free tool to migrate from MS SQL Server to MySQL:
mssql2mysql by Michael Kofler.
It is a Visual Basic script that can be run in any Visual Basic platform, such as the ones within Microsoft Office. The script connects directly with SQL Server, but requires MySQL ODBC if you want to create the MySQL directly in MySQL. However, you can create a MySQL script file, and thus no ODBC is required. So I did, and so I am explaining it.
It worked quite good for me, though I had to perform some changes in the code, because of charset issues. Some special characters caused the script to stop when writting it in the file, so I added error handling to the ExecuteSQL() function. This error handler writes "ERRORXXXXXXXXXXXXX" when something cannot be written in the output file. Thus, it is easy to identify where an error occured, and fix it manually. I didn't try to implement an automatic solution, as there are very few errors. I had four errors in a 31 MB sql file.
You had to custom the initial constants. If you want to migrate a SQL Server database from a local server to a file (instead of directly to MySQL), you just need to change the constants MSSQL_DB_NAME and OUTPUT_FILENAME. If your database has many records, you may want to try first the script limited, setting the MAX_RECORDS say to 1000.
The only output of the script's result is a Done message box, if everything went fine.
Note that the Visual Basic script creates the auto increment fields, but the sql script crashes when run, because the auto increment fields are not set as primary keys, what is a requirement of MySQL.
If everything went ok, you have by now your MySQL script. You just need to run it into MySQL and the database with all its data will be generated :)
(The SQL Server database to MySQL database migration script is attached as mssql2mysql.txt.)
I hope this notes may be useful for anyone.
Alberto Moyano Sánchez