MySQL to MS SQL (SQL Server)

Recently I produced a website for hosting on a Linux box running with PHP5 and MySQL5. Well it now needs to move over to a Windows 2003 server with MS SQL as the DB and IIS as opposed to Apache.

• Install MyODBC
• Install SQL Server 2005 Express

1. Using MS SQL Server Management create a new database. (right click on databases in the Object Explorer)
2. Use the following SQL Server SQL to create a link to your MySQL DB:

   http://gist.github.com/294726

   Execute the above

3. Use the following PHP script to get a list of tables to import:

   http://gist.github.com/294727

4. Copy and paste the output from the above script into your SQL Server query window and execute it
5. All your data should now have been transferred across along with the table schemas

Don’t forget to change all the references above to your correct database server settings.

Please note I have had some trouble with SQL Server not accepting 0000-00-00 00:00:00 in datetime fields set to not null in MySQL – set your datetime fields to accept null before exporting.

Also primary keys and auto_increment column attributes are not brought across either. So you will need to go into Server Management and manually re-add your auto_increment (Identity in SQL Server speak) and the primary key. It can also be done programmatically like this:

http://gist.github.com/294728

Because SQL Server doesn’t allow you to alter a pre-existing column to be an Identity (auto_increment) column you have to:

1. Create a temporary table with an Identity column
2. Allow arbitrary numbers to be inserted into the Identity column (includes previously inserted IDs apparently so you could end up with duplicates!)
3. Move the data across to the temporary table
4. Disallow inserting into the Identity column
5. Drop the original table
6. Rename the temporary table to the same name as the original
7. Alter the Identity column to add the primary key

Wow! What a pain.