If you've installed multiple instances of SQL Server 2005 on the same database server, perhaps you've noticed Microsoft's naming convention for the file locations. They are now using MSSQL.n, where n is the instance number. We have 4 instances installed on a database server. One of them is the default instance; the other 3 are named instances. Here is what our directory structure looks like:
E:\MSSQL.1\MSSQL\...
E:\MSSQL.2\MSSQL\...
E:\MSSQL.3\MSSQL\...
E:\MSSQL.4\MSSQL\...
This quote, from this article, explains the naming convention:
The first instance ID generated is MSSQL.1; ID numbers are incremented for additional instances as MSSQL.2, MSSQL.3, and so on. If gaps occur in the ID sequence due to uninstalls, ID numbers are generated to fill them. The most recently installed instance may not always have the highest instance ID number.
I didn't install the default instance until I was done installing the named instances, so the default instance is MSSQL.4.
Today, I was restoring databases onto this server from a SQL Server 2000 server. I needed to change the path to the database files as the source paths were E:\MSSQL$InstanceName\DATA\, but I now wanted them to be E:\MSSQL.2\MSSQL\DATA\ since MSSQL.2 corresponds to the instance where I am performing the restore.
But is there an easy way to find out which instance name belongs to which instance number?
The values are stored in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL. SQL Server has an undocumented stored procedure that allows you to read the registry, so we can use that to easily figure this out.
Here is one way to find out which instance number belongs to which instance name:
EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', 'InstanceName'