Using PowerShell to restore Missing Windows Installer cache items

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.