You know what it’s like, you are running a service pack on SQL Server and 99.9% of the time it all runs smoothly but that odd 0.1% of the time when it doesn’t is usually in the middle of the night with no one around to call on.
Well to spare you some pain I’ve discovered a really cool utility to help mitigate risk.
I was recently doing a service pack, I’d run it on the entire test estate and half of the Production estate, I’d used Pinal Dave’s really useful AG check list and been really overcautious, I’d finished integrity checks on all databases, I’d backed everything up and even had a snapshot of the server completed.
When I clicked on the service pack I got the error “missing MSI” sorry I don’t have the screen dump or the full error message because at the time of trying to fix it I did’t think to take a copy ☹
I understand the prime reason for this happening is that the installer files had been removed.
Anyway after a lot of bingling I discovered this blog from Microsoft which gives you the link to download a really cool little utility that can help you fix the missing MSIs, or at least help you identify them.
I would strongly recommend running the utility while you are preparing for service patching so that you can resolve any issues rather than having to deal with it in the middle of the night.
When you run the utility it will ask you for the installer drive, by default this is c:\windows\installer it may return something like this:
Hopefully it will return an empty table, however if it does return data we know what to do to fix it.
You can see that in the screen dump I’ve fixed two missing msi’s and failed to fix 3. However this does give us really useful information. We know what the msi we are missing relates to and the number SQL Server has given to it. SQL Server randomizes the CachedMSIMsp so you can’t just find that one.
Instead what you have to do to fix this is search out the package name, sql_is.msi, by the time I’d got to this point I’d downloaded the version of SQL Server I was working with and tried a repair which had failed. Search for the package name, so looking at the first one it’s sql_is.msi, copy it to c:\windows\installer and rename it to, in our case, 83717132.msi, repeat this for all missing msi’s and then you should be able to re-run the service pack successfully.