Friday, September 22, 2006

Back from the South

Over the past few weeks I've been working for a sizeable commercial bank down 'sarf'.

They’ve a computer setup that had been giving cause for concern and I was in to help them rebuild it onto new hardware.  As is usual this was interesting (in the Chinese sense!) but also rewarding, however we did get tripped up by an interesting dilemma.

The system was based in part on a an old SQL Server Cluster from which we needed the databases.  This was MSDE running on Windows 2000 Server.  The new environment is SQL 2000 running on Windows Server 2003.  The DBA's out there might know where I am going with this already… The original system was fragile and still running the original RTM version of SQL Server 2000.  The new system had to run Service Pack 3 as a minimum.  So late one day just as we went to do the restores and leave things running we hit a snag.  We could not upgrade the live system to SP3, and we could not run the new system on RTM.  We had to backup and restore the entire system including Master, MSDB, and Model databases.

So we resorted to an intermediate server - Windows Server 2000, SQL Server 2000 RTM and then restored the data, then we upgrade the server to SP3, backed it up, and restored to the new system.  A few extra hours at the end of a long day.  But a great gotcha!

The reason why - well Microsoft changed the Schema on the Master database in SQL server between RTM and SP3 and therefore the master database cannot be restored onto any machine running a different service pack.