Whenever it is time to install a service pack every DBA silently hopes that nothing will go wrong.  I would surmise that, in most cases, installing the service pack is a non-event.  No flashing lights, no fireworks, no applause and no major hiccups.  This post is not about one of those times.

The Install

Several weeks ago I attempted to install SQL Server 2008 R2 Service Pack 3 on one of our production instances.   The server was already at SP2 so it was to be a routine install. However, as the installer launched, this error appeared:

The issue stems from missing files in the installer cache.  When applications, including SQL Server, are installed certain files are placed within the installer cache.  These files are subsequently used when installing service packs and cumulative updates.  For some unknown reason b40c768.msi was missing.

My initial search lead me to a Microsoft Support page title “How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update“.  Perfect.   The blog post has a lot of detail in it and explains the issue fairly well.  It suggests two methods to help resolve the issue, one being a script and the other manual intervention.   I opted for the former, the script.

The Script

The instructions are straight forward.

1. Download the script to a folder on the affected server, IE: c:\temp
2. Open an elevated command prompt
3. Navigate to the folder where the script is located
4. Execute this command: Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt

The script will collect information about what packages may or may not be missing from the installer cache.  Note: that the output of the script will put a text file in the same folder as the script itself.  It will also overwrite any files that might already be present.  If you wish to run this multiple times, I would suggest changing the file names accordingly.

The output of the script looks like this:

Click to enlarge image

You will see that in the above example, it’s shouting at me stating that the MSI file doesn’t exist in the path D:\x64\set\sql_engine_core_inst_msi\.  This is because the install media isn’t located within the CD-ROM drive.  This was expected.

You will further notice that it says that the installer cache file, b40c756.msi was located and no further actions are needed.  If this particular cache file was missing the output would notify me.  I would utilize the command it provides to copy the missing msi file from the install media to the location it specifies.

Now that I knew where the missing file might be located, I set out to go find it.  In this particular case, I located the file on the Service Pack 2 install media.  If you weren’t aware, those service packs (and cumulative updates) are self-extracting files.  You can manually extract the files to a location of your choice so that you can locate the file you need.

Once I was able to locate the missing MSI file from the extract contents, I was able to successfully copy it utilizing the command that is provided in the output file.

At this point, it was rinse and repeat.  I would follow the same steps for each file that was missing from the installer cache.  All in all, there were 3 or 4 files that were missing.  Once I had all of the files replaced, I re-ran the SP3 installer file.

Then this happened.

Looks like we have another issue.  This error was much more cryptic and not very useful.  So of course, I went back to Google and did some more research.  In addition to researching, I also checked the event view on the server.  Nothing of interest was there.   I also thought to check the bootstrap log.   Here I found an interesting error:

Click to enlarge image.

You will note that the error happened right after it started to look at sql_as_CPU64, or b40c73a.msi (two lines above the censored section). I thought that I had mis-copied this particular file.  I tried to copy it over again.  That did not solve the issue.  I still got the same error.

I eventually turned to a forum I frequent, Ask SQL Server Central.   You can read the question that I posted here.  Unfortunately, I did not get any responses so that meant that I had to continue to try to fix it.  I really did not want to call Microsoft Support but I would if I had to.

TL;DR

The answer lies within the bootstrap log.  The error isn’t referring to the line above it as I had thought.  The “sql_as_CPU64.msi” file was processed just fine.  The error was occurring on the file AFTER that one.   However, you will notice that the file it is trying to consume is not listed. Using the bootstrap log, I was able to comb back through it looking for all of the missing installer cache files.  I finally found one that wasn’t accounted for within the log file, so that had to be the one.

Turns out, when I copied the MSI file from the source media I mistaken copied an MSP file, which is distinctly different.  I found the correct file and copied it back over to the server.

A relaunch of the installer lead to…..Success!

Resources

Here are some additional resources that I found.  These are not listed in any particular order.

• https://woodler-public.sharepoint.com/Lists/Posts/Post.aspx?ID=5
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6c539fe4-2aeb-4e4f-bef7-311e3f863ef3/sqlruntoolsmsi-install-error?forum=sqlgetstarted
• http://wadingthrough.com/2009/09/14/problem-installing-sql-server-2008-on-windows-2008-r2/
• http://blogs.msdn.com/b/sqljourney/archive/2013/06/13/sql-server-patch-fails-with-quot-could-not-find-any-resources-appropriate-for-the-specified-culture-or-the-neutral-culture-quot.aspx
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1c12de2f-8ed9-41dc-8e49-835af1aaddb6/sql-2008-r2-sp1-will-not-install?forum=sqlsetupandupgrade
• https://social.msdn.microsoft.com/Forums/windowsserver/en-US/98094885-f1f0-49f8-bd67-a5f2911ffd26/sql-2008-r2-sp3-ugrade-failure?forum=sqlsetupandupgrade
• https://social.technet.microsoft.com/Forums/sqlserver/en-US/fda9a67b-3688-4f9c-b45f-a1d2e3c0c80b/upgrade-sql-server-2008-r2-rtm-developer-x64-to-sql-server-2012-rtm-developer-x64-fails?forum=sqlsetupandupgrade

Lessons Learned

I learned a number of lessons throughout this process.

1. Pay attention to the details.  There is a distinct difference from a MSI and MSP file.  The former is an installer file.  The latter is a MSI patch file.
2. Keep note of things tried.  I found that this issue was a combination of several different things and I should have kept better documentation on each one.  I found that I would repeat steps that I had already tried because I did not document them well enough.
3. The internet is your friend.
4. Do not be shy to use the resources you have available to you, IE: forums.  They are there to help you.