Reading the SQL 2005 instance and installation directory from a DOS batch file

SQL 2005 installs each instance to a numbered directory based on the order installed: MSSQL.1, MSSQL.2, etc. That makes scripting a little tricky. Fortunately, you can find them in the registry.

The install path for the SQL 2005 database server is here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2005\Setup\SQLPath

You can look up specific instances in the the Instance Names registry node. For instance, to find the SQL2005 instance, you'd look at this key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\SQL2005

Looking up registry values in a DOS batch file is a little tricky. Fortunately, Robert VanderWoude has a great tip for reading registry values via creative use of the FOR /F command.

So, with all that out of the way, we can read the SQL Server instance with this line:

FOR /F "tokens=2* delims=     " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" /v SQL2005') DO SET SQLINSTANCE=%%B

and the SQL Server base installation directory with this:

FOR /F "tokens=2* delims=     " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2005\Setup" /v SQLPath') DO SET DATADIRECTORY=%%B\Data

Why would you want to read that kind of stuff in a batch file? Well, to write a Batch files to check SQL 2005 (MDF) files in and out of Subversion source control, or course.