Tuesday, February 6, 2007

How to Upgrade SQL 2005 x64 from SP1 to build 2153 (KB918222)

If you don't know what this topic means skip the rest of this post. If however you're here because you're trying to upgrade from 2047 (SP1) to the latest hotfix -build 2153- and you're getting nothing but "Failed" then this is for you.

First things first; read this again:
http://support.microsoft.com/kb/918222

Do things in the order they suggest because why not? Why fight with things for the sake of fighting with them? Oh what's that- you installed the client patch first? Heh... me too. It doesn't really matter. ;)

So anyways, I'm sure you want to know the fix so here goes.
First, figure out where your mssqlsystemresource and distmdl log and data files are. If they're all in the same folder as your master db then you've got a problem that I can't fix with this post. But if you (like me) got this crazy idea somewhere that logs and data should be kept on separate volumes and you happen to do that for system dbs as well because oh, I don't know; you like order, then I may just have the asdfnswer you're looking for.
This problem may or may not be a 2-step fix for you.
First, look at where your master.mdf is- are distmdl.mdf and distmdl.ldf there? No? Copy them there! Try it again- if the upgrade works you're done.

The other problem you may run into is the upgrade of the mssqlsystemresource db. If the .mdf and .ldf files are in different dirs (or more likely; volumes) then you'll have to copy the newer log (.ldf) version that it dumps in the same dir as your mssqlsystemresource.mdf over to the correct location before the upgrade will work. Of course your server needs to be down for this to happen. FYI: the newer file has a date with a timestamp of around 12:13am... I can't remember the exact date but the older version has a timestamp around 3-something am. If you don't normally keep the mssqlsystemresource.ldf file in the same dir as your mdf then you shouldn't have even read this far- go upgrade your system!!

And just in case these didn't help you here's some more advice:
You can find most problems by checking: %windir%\hotfix\sql9\
SQL9_Hotfix_KB918222_sqlrun_sql.msp.log
For "value 3"... then look for something about failing to access a file (note the location) and/or any other messages that may help you out. One stupid thing is that the error will state the installer doesn't have access to a file when in reality the file isn't there. Use your head and check it out before assuming it's a permissions issue.

No comments: