Converting a SQL Server Database to MySQL

Table of Contents

In preparation for an upcoming database migration -- converting a web site from a homegrown CMS to Drupal -- I've been spending a fair amount of time learning Drupal's Migrate module. Although I knew that Drupal 7 could connect to SQL Server, upon further scrutiny I found out that the sqlsrvr module requires the PHP SQL Server Driver 2.0 from Microsoft, a Windows-only driver. Since I work primarily on a Mac, I decided to first convert the SQL Server database to MySQL. There are a few ways to accomplish this, and this post is mostly for my reference, but I thought others might benefit, too.

INSTRUCTIONS

  1. On a Windows machine, download Michael Kofler's VB script called mssql2mysql.txt, add your credential per the instructions, and run it in VB6, Excel, or another VBA editor. This script copies your entire database, and the first time I ran it I ended up with a 350 MB file containing over 1.1 million SQL statements. Even Vim felt that file was a bit big for editing. Since I needed just a few dozen tables from the database, I added two extra "if statements" to the VB script to pull only the tables that began with a certain prefix, and those looked something like this: If Not db.Tables(i).SystemObject Then Dim strTablePrefix As String strTablePrefix = db.Tables(i).Name If Left(strTableName, 9) = "MyTables_" Then sql = TableDefinition(db.Tables(i)) ExecuteSQL sql End If End If If Not db.Tables(i).SystemObject Then Dim strTablePrefix As String strTablePrefix = msdb.Tables(i).Name If Left(strTableName, 9) = "MyTables_" Then CopyTable msdb.Tables(i) End If End If Pulling just the data I needed was a good idea as the filtered file was 1/100th the size of the original file.
  2. Transfer the execute.sql file created by the VB script to your Mac and clean up the file, if necessary. For example, MySQL does not support tables with more than one AUTO_INCREMENT column, and since I wouldn't be adding any data to the MySQL database after I converted it, I removed all instances of AUTO_INCREMENT: :%s/ AUTO_INCREMENT//g Also, to replace MS "smart quotes" I need do the following substitutions: :%s/<92>/\\'/g :%s/<93>/\\"/g :%s/<94>/\\"/g On my Mac, I had to type CTRL-V x 92/3/4 to get the <92/3/4>.
  3. Run the SQL script in MySQL. For my development site I had been using Acquia Drupal, so from the command line, I ran this /Applications/acquia-drupal/mysql/bin/mysql -u user -p and entered my password. From the mysql prompt, I entered source export.sql to import the data.
  4. Done! This worked for me. Your mileage may vary.

Comments