This seems oddly specific, but someone or something had cleared out many files from the Windows\Installer folder, probably to save space. (Sidenote, you can actually create a mount point for these that go on another drive as a workaround. Not recommending it, but it’s an option when given a really small system drive that can’t be changed.)  One of the side-effects of these missing files was the inability to patch SQL Server. When trying, I got a message indicating that a given *.msp file was missing and it should have come from some other msp file for specific MS KB.  In this particular case, the machine was missing > 400 Installer files. It turned out the ones I needed were all from one of the SQL Server Cumulative Updates.

After much reading/searching, I came across Ahmad Gad’s post and script to more easily restore these files using Windows PowerShell. He has a PowerShell script linked in his article to a function called Restore-InstallerFiles.ps1 that can look at a given set of folders or at another machine.  The first thing I did was point this at another server with the same Windows/SQL combination.

#Connect to Machine1 to search its Installer cache for missing files
#Needs to run in elevated/admin PowerShell session and needs Admin rights on remote machine
.\Restore-InstallerFiles.ps1 -SourceMachine "Machine1"

That found a set of files and was able to obtain the vast majority of the needed files.  Unfortunately, it did not get the ones I needed. I extracted the CU into its own folder using a command line call to the EXE file with the “/x” option. That prompted me for a location to extract the files. I gave it a separate folder with a shorter path to navigate.

I then pointed Ahmad’s script at that folder and … nothing happened.  I dug into that error message some more, searched for the exact MSP file needed, then pointed the script at that folder. I restored one MSP file because the MSP files are stored in well-organized folders and Ahmad’s script doesn’t have a way to traverse all of the sub-folders.  At that point, I did a search on the extracted files for all *.msp files and copied them to their own folder. I re-ran Ahmad’s script, pointing to that “MSP” folder and restored the needed files.  That machine was still missing quite a few files, but I figured I’d caught the SQL-related files so tried my patch again.

#After extracting the CU into a folder, found all MSP files and copied to a single folder
#Run the function again, pointing at that folder
.\Restore-InstallerFiles.ps1 -SourceFolder "C:\Temp\MSPFiles"

This time, with all of the necessary Installer cache files restored from other servers and the extracted CU files, the patch succeeded.

Edit: I’ve created a gist for the “Restore-InstallerFiles.ps1” script as Ahmad’s post/file seems to have gone away. The link above should be updated.