SQL Server 2005 update failure

I recently had to apply a patch to a SQL Server 2005 machine, the patch was KB970892. However it failed, I would like to detail what I found and why the install failed.

SQL Server SP3 had been applied to the SQL Server instance

Post initial installation of SQL SERVER I had move the MASTER, MODEL, MSDB and TEMPDB to new locations. As it turns out this is what caused  me the grief. However trying to get to that point was a journey.

The initial log that the failed update asks you to review is massive, and I failed to see any useful failure information from that log. However while trawling the internet I came across the following log file

%programfiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Summary.txt

The error I got in this file was

MSP Error: 29538  SQL Server Setup did not have the administrator permissions required to rename a file: d:\SQLData\mssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.

It was this file that began to lead me to what was going on.

I eventually came across this forum post.

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/fc411c55-f26b-48d4-9bf7-a333a87bc6de?prof=required

So what is going on, well it appears that if you move the master.mdf file, you need to manually move the following mdf and ldf files to the same location. These files are support files I believe for various things like replication.

• mssqlsystemresource.mdf
• mssqlsystemresource.ldf
• distmdl.mdf
• distmdl.ldf

e.g

master.mdf moved to d:\sqlData\

I had to move the four files listed above to this folder. Then the update ran successfully. I realise the error message has a 1 in the filename, but I did not rename my files to match and the update completed successfully. That said the files remained untouched!

Hope this helps someone.

SQL Server 2005 update failure