Restore the missing Windows Installer cache files SQL Server service pack upgrade

The cached MSI file 'C:\Windows\Installer\******.msi' is missing.
Its original file is 'sql_engine_core_inst_loc.msi' and it was installed for product 'SQL Server 2008 R2 Database Engine Services' 
from 'E:\en_sqlserver2008r2ent_x86_x64\1033_ENU_LP\x64\setup\sql_engine_core_inst_loc_msi\'

This error typically occurs when you are trying to install a service pack for a SQL Server instance. The reason for this is simple and I am not sure why Microsoft won't fix it. This is more to do with the Windows installation methods.

Typically the SP file that you download is an executable like SQLServer2008R2SP3-KB2979597-x64-ENU.exe
When you run this it will temporarily extract the files to a directory with weird names like
accb93ea9c80345f38c8b49a3c
efe153398dc16fa539a10f08451096f0

The windows admins might clean up these directories at some later point and this causes the above error. No one should clean up these drives or C:\Windows\Installer. Now that these files are not available you will have to retrieve them from the installation media. Typically you will need the installation files for the SQL Server version and all subsequent SPs and hotfixes that were applied. The steps to retrieve them have been described here
https://support.microsoft.com/en-us/kb/969052

To fix the error do the following

• Download the file FindSQLInstalls.vbs from the Microsoft support page and then run the following command
• Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt
• Check the text file and following is an example of one file that is missing and how to resolve it

============================================================

PRODUCT NAME   : SQL Server 2008 R2 SP2 BI Development Studio

============================================================

  Product Code: {312E8540-0799-45D5-A02E-DFB8FCA93CCA}

  Version     : 10.53.6000.34

  Most Current Install Date: 20160310

  Target Install Location: 

  Registry Path: 

  HKEY_CLASSES_ROOT\Installer\Products\0458E21399705D540AE2FD8BCF9AC3AC\SourceList

     Package    : sql_bids.msi

  Install Source: \x64\setup\

  LastUsedSource: n;1;Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\

 

 !!!! sql_bids.msi DOES NOT exist on the path in the path Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\ !!!!

  Action needed, re-establish the path to Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\

 Installer Cache File: C:\Windows\Installer\2be74f.msi

  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 !!!! C:\Windows\Installer\2be74f.msi DOES NOT exist in the Installer cache. !!!!

 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

     Action needed, recreate or re-establish path to the directory:

       Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\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 "Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\sql_bids.msi" C:\Windows\Installer\2be74f.msi

============================================================

Look for the line where it says LastUsedSource. This is where SQL Server expects the file to be. So in my case I had to copy the installation media for SQL Server 2008 R2 to 

Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2

You can create this directory or map the directory where the files exists.

If you rerun the vbscript again you will see messages like this

 

============================================================

PRODUCT NAME   : SQL Server 2008 R2 SP2 BI Development Studio

============================================================

  Product Code: {312E8540-0799-45D5-A02E-DFB8FCA93CCA}

  Version     : 10.53.6000.34

  Most Current Install Date: 20160310

  Target Install Location: 

  Registry Path: 

   HKEY_CLASSES_ROOT\Installer\Products\0458E21399705D540AE2FD8BCF9AC3AC\SourceList

     Package    : sql_bids.msi

  Install Source: \x64\setup\

  LastUsedSource: n;1;Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\

 

    sql_bids.msi exists on the LastUsedSource path, no actions needed.

 

Installer Cache File: C:\Windows\Installer\2be74f.msi

============================================================

Notice it says 'no actions needed'. The Vbscript will copy the corresponding msi files to the C:\Windows\Installer directory

If the msi file belongs to a service pack installation then you will see something like this.

 

SQL Server 2008 R2 SP2 BI Development Studio Patches Installed 

--------------------------------------------------------------------------------

 Display Name:    Hotfix 2806 for SQL Server Business Intelligence Development Studio 2008 (64-bit) (KB2659694)

 KB Article URL:  http://support.microsoft.com/?kbid=2659694

 Install Date:    20120702

   Uninstallable:   1

 Patch Details: 

   HKEY_CLASSES_ROOT\Installer\Patches\1C168ABFC95177B41873A43674A1BAFF

   PackageName:   sql_bids.msp

    Patch LastUsedSource: n;1;r:\8e04aef200a6f6509400f3f8270e52\x64\setup\

   Installer Cache File Path:     C:\Windows\Installer\e21fb.msp

     Per SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\1C168ABFC95177B41873A43674A1BAFF\LocalPackage

 

!!!! C:\Windows\Installer\e21fb.msp package DOES NOT exist in the Installer cache. !!!!

 

     Action needed, recreate or re-establish path to the directory:

       r:\8e04aef200a6f6509400f3f8270e52\x64\setup\ 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 missing files or manually

     replace the problem file, 

 

     Copy "r:\8e04aef200a6f6509400f3f8270e52\x64\setup\sql_bids.msp" C:\Windows\Installer\e21fb.msp

Note the KB2659694 and if needed download it which in my case was SQLServer2008R2-KB2659694-x64.exe

Now on command line extract/uncompress the files using this exe to a temporary directory. The option /x is to uncompress the file to a directory.

SQLServer2008R2-KB2659694-x64 /x

Once you have uncompressed the files copy the directory to the location where the files are expected and rename it to whatever directory it expects. In the above example it expects the following directory

r:\8e04aef200a6f6509400f3f8270e52

So copy the uncompressed directory for this SP to R: drive and rename the directory. You can confirm that the files is there by going to 

"r:\8e04aef200a6f6509400f3f8270e52\x64\setup" 

Rerun the vbscript and confirm that for the files it says that msi file exists and no actions are needed.

Copy the entire installation directory and not individual files like they mention in the support page.

You might have to do this for the following

• SQL Server installation media
• Service pack files that were applied
• Hotfixes that were applied.