One would have thought that applying a Service Pack is an easy job. Well not exactly as I found out. I spend two days trying to apply the patch.
The main problem as I noticed from the log was that the installer was looking for a .msp file which did not exist.
Solution
Uninstall SQL Server completely using the Windows Installer Cleanup utility - http://support.microsoft.com/KB/290301.
Once all the components of SQL Server have been uninstalled. Install SQL Server from the original source - could be the DVD or the downloaded software. During installation you may get an error about owc11.exe. Uninstallation of this is not possible through the Add/Remove programs in Control Panel. This will have to be uninstalled by using the Windows Installer Cleanup utility. Once this is done re-run SQL Server 2005 setup and it should go through.
After installing SQL Server, install SP2. Hurrah! I had my SQL Server up and running and what was surprising was I found all the databases intact. Obviously, it is still a good idea to backup your databases before trying this out.
