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.
#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.
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others.
I currently work for a Health-related non-profit based in Boise as a Database Architect.