Quickduck

Recently I made the unfortunate mistake of developing against an SQL Server 2005 database server, which I mistakenly thought was an SQL Server 2000 server. I know what you’re thinking, “how could you get that wrong?!” – but the reason was that the server was previously running 2000, and I had forgotten that it was upgraded.

Anyway, this wasn’t really a problem (since I had compatibility level set to 8) – until it came to deploying the updated database. Turns out you can’t export a 2005 backup and restore it onto 2000 – no real surprises there! So my next option was to generate scripts to recreate the database on a SQL Server 2000 server, but guess what – the Generate Scripts tool in 2005 does NOT generate scripts compatible with 2000. Now that was a surprise, given that one of the options actually allows you to specify that the scripts are for Sql Server 2000!

After searching around the net for a solution, I concluded that my best option was to restore an old database backup, and then manually go through and make the changes. Clearly not a good option, since it was very likely that I’d be missing things left right and centre, but what choice did I have?

Lucky for me, a colleague happened to pay me a visit just as I was about to start, and told me that he had had my exact same problem only a few weeks ago. Better still, he had a tool which was effectively a diff/merge tool which worked across database versions. Sweet!

So I checked it out – RedGate’s SQL Compare – and found it an absolute delight to use. It was able to update about 95% of the changes without manual intervention. The other 5% was also a simple change – since SQL Compare had generated a script to do the upgrades, all I had to do was make a few minor tweaks.

My only complaint with SQL Compare is that you can’t tell it not to synchronize/compare extended properties of objects. (Extended properties define stuff like layouts of diagrams, etc). If I could have turned this off, it would have been able to do the entire upgrade without any intervention from me. Still, overall, this is an awesome piece of software, and it gets my highest recommendation.