SQL Server MSP and MSI Missing Issue

How to restore the missing MSI (Microsoft Software Installer) & MSP (Microsoft Software Patch) issue which occur during a SQL Server update?
This is the common issue while upgrading SQL Server to new SP (Service Pack) or CU (Cumulative Update).

First of all, Lets discuss what are MSI's & MSP's?
An application that has been installed using the Microsoft Windows Installer can be upgraded by reinstalling an updated installation package (.msi file), or by applying a Windows Installer patch (an .msp file) to the application.
A Windows Installer patch (.msp file) is a self-contained package that contains the updates to the application and describes which versions of the application can receive the patch. For more information about the parts of a Windows Installer patch package, see For more details Patch Packages.

Problem

Symptoms of this issue is when we are trying to upgrade SQL Server, rather than going smooth as it goes always :)
Suddenly prompts window with below error message something like:
The cached patch file "C:\Windows\Installer\19b19196.msp" is missing. The original file for this cached file is "sql_engine_core_inst.msp", which can be installed from "Service Pack 3 for SQL Server 2008 (KB2546951) (64-bit)", version 10.3.5500.0

Cause

These problems may occur when the Windows Installer database file (.msi) or the Windows Installer patch file (.msp) is missing from the Windows Installer cache. The Windows Installer cache is located in the following folder:
%windir%\installer
When a product is installed by using Windows Installer, a stripped version of the original .msi file is stored in the Windows Installer cache. Every update to the product such as a hotfix, a cumulative update, or a service pack setup, also stores the relevant .msp or .msi file in the Windows Installer cache.

Resolution

There are two procedures to resolve this issue:
1) Restore Files Manually
2) Restore Files Automatically

Before Starting any procedure follow the below Steps:
1. Copy this VB Script
FindSQLInstalls.vbs script and save it on your Server in C drive or in any directory you want.
2. Open an elevated command prompt to the directory to which you saved the script, and run the following command:
Cscript FindSQLInstalls.vbs C:\Missing_File_details.txt
3.Open the file (Missing_File_details.txt) from step 2 in a text editor such as Notepad, and identify the problems that are causing the failure.

First, copy the SQL Server media files onto a local folder on the computer where you are trying to update your SQL Server installation.
Let suppose you are facing an issue while upgrading SQL Server 2008 R2 SP3 on SQL Server 2008 R2 SP2.
Or You can find which SQL Server Version MSP\MSI file is required:

================================================================================
PRODUCT NAME   : Microsoft SQL Server 2008 Database Engine Services
================================================================================
  Product Code: {9FFAE13C-6160-4DD0-A67A-DAC5994F81BD}
  Version     : 10.2.4000.0
  Most Current Install Date: 20110211
  Target Install Location: 
  Registry Path: 
   HKEY_CLASSES_ROOT\Installer\Products\C31EAFF906160DD46AA7AD5C99F418DB\SourceList
     Package    : sql_engine_core_inst.msi
  Install Source: \x64\setup\sql_engine_core_inst_msi\
  LastUsedSource: m;1;G:\x64\setup\sql_engine_core_inst_msi\

So copy the SQL Server 2008 R2 (from CD or Software repository) and all missing KB Articles media files.

In case you dont have then download it and extract using below command as example:

SQLServer2008R2SP2-IA64-ENU.exe /x:C:\SQLServer2008R2_SP2

Here now comes the two procedures to proceed further:

1) Restore Files Manually

In this process,
1. Open the file (Missing_File_details.txt) from step 2 in a text editor such as Notepad, and identify the problems that are causing the failure.
To do this, search the text file for string patterns such as:
Copy "
You will find as Example:

Action needed, recreate or re-establish path to the directory:
     G:\x64\setup\sql_engine_core_inst_msi\
     then rerun this script to update installer cache and results
     The path on the line above must exist at the root location to resolve
     this problem with your msi/msp file not being found or corrupted,
     In some cases you may need to manually copy the missing file or manually
     replace the problem file overwriting it is exist: 
 Copy "G:\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi" C:\WINDOWS\Installer\19b4d2.msi
      Replace the existing file if prompted to do so.

2. You will find the copy statements as shown below:

Copy "G:\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi" C:\WINDOWS\Installer\19b4d2.msi

Now you have to locate "sql_engine_core_inst.msi" file in SQL Server media it should be into your directory where you have copied SQL Server media %\x64\setup\sql_engine_core_inst_msi\

3. Modify your copy command
let suppose you have copied it in the C Drive to "C:\SQLServer2008R2_SP2" location
so your modified command will be like:
Actual:

Copy "G:\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi" C:\WINDOWS\Installer\19b4d2.msi

Modified:

Copy "C:\SQLServer2008R2_SP2\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi"
C:\WINDOWS\Installer\19b4d2.msi

Verify the above path, you will find the "sql_engine_core_inst.msi" file in it.
So run the above command in an elevated command prompt.
Thats it!!
This you have to do for each Copy Command mentioned in the file (Missing_File_details.txt).
This is time taking as its manual perfer only if Automate process doesn't work.

2) Restore Files Automatically

Now here to automate this process, you have to Copy the SQL Server installation media and all missing KB Articles at the same location by create same folder name and directory as its mentioned in the file (Missing_File_details.txt).
Let suppose,

Example 1: If missing file source is G Drive and path as below:
So copy the SQL Server SP2 media file into the same directory as to mentioned in LastUsedSource: "G:\"
LastUsedSource is the directory where the files were initially hosted when we installed SQL Server or at the time of Upgrade.

================================================================================
PRODUCT NAME   : Microsoft SQL Server 2008 Database Engine Services
================================================================================
  Product Code: {9FFAE13C-6160-4DD0-A67A-DAC5994F81BD}
  Version     : 10.2.4000.0
  Most Current Install Date: 20110211
  Target Install Location: 
  Registry Path: 
   HKEY_CLASSES_ROOT\Installer\Products\C31EAFF906160DD46AA7AD5C99F418DB\SourceList
     Package    : sql_engine_core_inst.msi
  Install Source: \x64\setup\sql_engine_core_inst_msi\
  LastUsedSource: m;1;G:\x64\setup\sql_engine_core_inst_msi\

Action needed, recreate or re-establish path to the directory:
     G:\x64\setup\sql_engine_core_inst_msi\
     then rerun this script to update installer cache and results
     The path on the line above must exist at the root location to resolve
     this problem with your msi/msp file not being found or corrupted,
     In some cases you may need to manually copy the missing file or manually
     replace the problem file overwriting it is exist: 
 Copy "G:\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi" C:\WINDOWS\Installer\19b4d2.msi
      Replace the existing file if prompted to do so.

Example 2: Most of the time, you will notice entries in the "Patch LastUsedSource" line that reference a patch, and this line resembles the following:

Patch LastUsedSource:   n;1;c:\0ca91e857a4f12dd390f0821a3\HotFixSQL\Files\

This output indicates the following about the patch installation:
- The original patch was installed by double-clicking the patch's executable file.
- The installer for the patch used a temp folder, c:\0ca91e857a4f12dd390f0821a3, during installation of the patch.
- To re-create the path, you must run the same executable and add the following parameter:
/x:c:\0ca91e857a4f12dd390f0821a3
Thats it!!
Now start your upgrade again and it you will be able to apply smoothly.
ReferMSDN Article for more details.