Hi, Can anyone help me in finding the SQL server Root directory and error log location using a sql script for multi-instances, cluster and non cluster servers.
asked Jun 23 '11 at 07:22 PM in Default
SQL script will not be the best candidate for this.
I suggest you take a look for a PowerShell script. As the installation directory is stored in the registry and the log location is part of the startup parameters of the SQL Server service.
Anyway you can take a look here for the inspiration of the t-sql solution to retrieve installation directory: [How to find the SQL Server install directory from tsql]: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/04cc91e9-7dc3-462f-a2b7-23f00eaa863f/
I was playing around with some stuff on a cluster and using PowerShell you can actually use this to get your information (if you have PowerShell running on the servers of course). You will also need SMO loaded, which this is already there if SSMS is installed on the host machine.[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.SMO') |
$s = New-Object 'Microsoft.SqlServer.Management.Smo.Server' instance_name
$s.Information.RootDirectory | Write-Host -foreground Red
$s.Information.ErrorLogPath | Write-Host -foreground Red
$s.Information.MasterDBPath | Write-Host -foreground Red
$s.Information.MasterDBLogPath | Write-Host -foreground Red