How To Fix Error - "The Instance ID 'MSSQLSERVER' Is Already In Use By SQL Server Instance 'MSSQLSERVER.INACTIVE'

On the weekend, playing with SQL Server has always been my hobby. I have an entire lab set up at my home. I call it my "playground”. Recently, I was doing a upgrade from SQL Server 2008 to SQL Server 2014. Due to some error, the upgrade failed and as a last resort I had to re-install SQL Server. Un-install was clean. I started the installation process and when I selected instance type as “Default Instance”, I encountered a weird error.

“The Instance ID ‘MSSQLSERVER’ is already in use by SQL Server instance ‘MSSQLSERVER.INACTIVE’."

 

The error is pretty clear. All I have to do is remove MSSQLSERVER.INACTIVE to fix the issue. Here is the brief description of what should be done to fix the issue.

• Search your machine for files called Datastore_Discovery.xml (depending on how much you’ve got or done, you might see many of these…just open the most recent one and you’ll be fine.
  
  
• Open Datastore_Discovery.xml in an editor…given the formatting, what worked for me was in Visual Studio 2008/2010 and clicking Edit, Advanced, Format Document (this arranges the XML nicely)
  
  
  
  
• What you’re looking for is all places where MSSQLServer.Inactive exists in the document as the Instance ID. Below is a sample of how the line starts
  
  
• Scroll along the lines around the above one and find the following value field: ProductCode=”{9FFAE13C-6160-4DD0-A67A-DAC5994F81BD}. There might be multiple Product Codes, depending on how many times the <Instance URN…> line lists MSSQLServer.Inactive, so make sure to find all Product Codes.
  
  
• Now, open a command line and for each of the Product Codes that you found for the Inactive SQL Instances, type the following,
  
  
  

• Do this for each Product Code that relates to an Inactive Instance.
  
  
• Once complete, go back to your SQL installation media and run Setup.
  
  
• Once the main splash screen launches, click on Tools, then the installed SQL Server features discovery report.
  
  
• This will launch a web page listing the SQL Instances and all features per instance. If you still see any MSSQLSERVER.INACTIVE there, you missed one of the Product Codes and you need to double check. Otherwise, all of the orphaned SQL Instances should now be gone, and you can either Add Features or do a clean Default Instance reinstall.

I hope this will help everyone.