I have been meaning to move my community server blog and bits and pieces from WebHost4Life (fed up with the downtime!) to another hosting company for quite some time but never really got around to it mainly because I didn't want the hassle of moving my SQL databases.

Yesterday I finally decided to migrate my site and decided to use some software called SQL Packager written by the company I work for. Now, I support this software as part of my job at Red Gate but I have only really ever used it to recreate customer problems and have little experience of using it in anger and certainly not on my own data!

First step was to point SQL Packager at my existing SQL database and tell it to package up my database. In my case this pretty much meant just accepting all the defaults and off it went. At the end of it I had my schema and data all wrapped up in a executable file on my hard disk.

Next I created a new SQL database on my new host server and ran the executable that I had just created. I provided my new database details and off it went... until it fell over with a collation error. As SQL Packager provided me with details of what the original database collation was it was easy to change the settings on my new database to match it and I re-ran the executable.

Hurrah! In less than 15 minutes I had migrated my SQL databases from my old host to my new one and it all worked - THAT WAS EASY!!!

I just wish copying the files across from my old site to the new one was as easy. For some reason some of the files didn't get copied across and I spent ages messing around trying to work out what had gone wrong. So, I know now that moving my databases is going to be easy but now moving the files has become the point of pain. To solve this, I thnk I'm going to have to put in a request in at work for a new product - Red Gate Web Site Packager

BTW.
Just to make it clear, I do work for Red Gate Software who make the SQL Packager software but this blog is mine and this entry is not intended as an advert or anything!!