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.