Patching is an activity that is frequently performed by DBA’s. It is the responsibility of a DBA to keep the SQL servers up and running all times and ensure the Cumulative Updates, Service Packs, out of band hot-fixes are applied to the instances.It’s hardly a 30 min task if it involves a cluster. Procedure is simple download the patch, extract the files and run it on the nodes (passive first) and bounce the services back to active node.
However every once in a while it’s good to come across some errors and get in the tight spot of now what? Take a look at the below error.
As the error states a possible Windows Installer database file (.msi) or the Windows Installer patch file (.msp) is missing from the Windows Installer cache. This normally occurs when the setup is stopped mid-way or due to file corruptions. Below is an amazing support link by Microsoft which explains the cause and resolution for the above error. It also provides a vb script FindSQLInstalls.vbs that helps find the missing .msi and .msp files.
Step 1: Follow the Microsoft support link
When the script is run with admin privileges the log generated directs from where to copy the missing files (SourceFolder) and where to place them (Destination Folder). The <RootDrive> is location from where the setup was run or the patch files exists. They could be on different drives as well. If you observe carefully the folder <SQL Server **** DO_NO_DELETE> gets created on new installation to the respective drive.
Most of the times the source folder will look as follows
Source Folder :(RootDrive)\x64\setup\
Source Folder :(RootDrive)\1033_ENU_LP\x64\setup\
Destination folder - C:\Windows\Installer
In the scenario that was encountered the files were missing even at the path mentioned by the log generated with FindSQLInstalls.vbs for source locations. So what next ?
Since we corrupted the files this time it did not tell us which files were skipped or missed the setup just shut down with the above message. At this point of time I was able to look up Aaron Bertrands blogs for uninstallation instruction for SQL server evaluation edition.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\
Step 2: Try uninstall –re-install
Well now that we don’t have the files mentioned at the source location we look through search option. After searching for the files on the local machine on all other drives as well we fail to find them. So now we are stuck. Next option at hand is uninstall-re-install. Obviously take the mandate backups of databases system as well as users. Take backups of reporting servers if configured. Back up any other files which will be cleared during uninstall. When trying to uninstall SQL server we get the below error.
Step 3: Uninstall each SQL components from add or remove programs under control panel.
Well it doesn’t get easy does it? But have to fix it so we try this and again we get an error.
At this point of time we created a new folder with a clean copy of setup files and point the browser to the new location with the new setup files it still would not take the path.
Step 4: Do not panic and do not try this on production servers.
Almost about to give up after this error when we do what we are not supposed to do. If you have read above Microsoft link carefully it clearly states missing files cannot be copied between computers, because they are unique. We still proceeded with trying to cheat the computer and copied those files from a different server.
Warning: Never try this on production servers. Be prepared for an instance crash as well.
End result: Corruption of files under C:\windows\installer directory.
Step 5: Undo step 4
Thankfully we had made a note of which files were copied & where the files were copied from one computer to another and deleted them. Not knowing that the system was smart enough to understand we tried to cheat it. It had also created some files of its own under C:\Windows\Installer directory.
So now we are back at step 3 with even more corruption.
Step 6: Look up registry commands
Desparate to fix this I try to uninstall the instance again. Get the below error
Since we corrupted the files this time it did not tell us which files were skipped or missed the setup just shut down with the above message. At this point of time I was able to look up Aaron Bertrands blogs for uninstallation instruction for SQL server evaluation edition.
Prior to executing any of the below step I recommend one to go through these links as well
Now we proceed to lookup the registry mentioned in Aaron's blog to locate all of the SQL server -related GUIDs
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\
We go through each folder, note down the display name and uninstall string msiexec.exe command for all SQL related GUID's.
Step 7: Fix the components with registry
In Aaron blogs he wanted to uninstall the instance. We on the other hand are just trying to patch the existing one which we managed to corrupt further. So let’s fix what we broke. We have already copied the clean setup files in the root drive.
We do not run the setup just extract all the files. Next we run the misexec.exe command for each component. Again it throws me the windows installer error.
We click on OK to see which file its asking for ? Next we go to the new setup files location where we have our setup files extracted. This time it agrees to take the path and we hit OK.
This time it installs the feature successfully and creates the GUID files under C:\Windows\Installer as well.
Step 8 : Try to run the patch again.
Now that we have fixed all the individual components we have all the files required by windows installer for upgrade.Hence we proceed with running the patch once again. This time we are able to do it successfully.
Mission accomplished & a lesson well learnt ........
