Say you have a development environment setup using SQL Server 2005 Express Edition and your customer has a SQL Server 2000 database accessible only via ODBC and you can only run a DB import via a script. How would you do it? Easy you might think just run msdbdump.exe on the command line, well this isn’t MySQL so you are not so fortunate.

Backing up your DB is easy with SQL Server using the following commands:

http://gist.github.com/294721

But this just gives you a binary file that can be restored if you have access privileges to the live database for restoring. If you are in a shared hosting environment or one where the paranoid admins won’t give you remote desktop access and the only access you have is to run a PHP script to import the data and schema via SQL you will need to export the DB to T-SQL format.

Microsoft have a little program to perform this very function: Microsoft SQL Server Database Publishing Wizard 1.1 It is difficult to find on the web so I aim to save you the time I spent hunting for it. When you run the wizard make sure to set:

• Drop existing objects in script to false
• Schema qualify to false
• Script for target database to SQL Server 2000

It does seem to chew on the cud for quite some time so grab a beverage.

Now for the PHP portion of the process. So you have uploaded your lovely T-SQL dump file to a PHP accessible location on your webserver and now you are wondering how to get into your DB via ODBC. Well you will need a PHP script like the one I have supplied below.

A couple of the complexities to be aware of before you continue. It seems that the T-SQL dump file comes out as UTF16 and we need it in UTF8 so you will need to convert it to UTF8 before you can import. I used a neat little function available from Modular.org for this purpose. This may or may not meet your needs. If you need a more accurate conversion method then I recommend you start your search with the PHP module/function mbstring . T-SQL contains reference and keywords that ODBC/MS SQL cannot understand. I have included some regex to strip these out.

My script is by no means perfect or factored down so feel free to make suggestions or improvements.

http://gist.github.com/294720

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.