14 December, 2010

Find SQL Default Data and Log Directories using TSQL Independent of Instance name or Cluster Installation


Hello, I was needing this function for some reason and I though someone else may need it.

So basically this info we are wanting is the default place where SQL will place the Data and Log Files, this will be brought when you try to restore a database using the GUI, if you’re not using the GUI you’ll have to know the directories.

Well SQL just doesn’t have a Custom Function for finding these directories, its not contained on SQL itself, SSMS shows you if you right-click the server you’re connected and select properties, then selecting Database Settings, but other than that the only way to know this info is by looking it up on the windows registry.

So how do we need to look in the registry with TSQL, we also need to check the right place because if you are running a named instance this values will be on a different place that they would be if we were querying a default instance, the same for a clustered instance.

So How do we go through this?

Well there's a procedure called XP_INSTANCE_REGREAD, if we were just reading any key on the registry we would be using xp_regread, but we need this info regardless of instance, so this xp_instance_regread will do the job of localizing which instance we’re currently using.

Now we need to query two registry keys one for the data directory and another for the log, however on cluster installations the Data directory is located on another key, so just using xp_instance_regread won’t save us this time, we’ll have to manually check for this. So on the code I expose bellow I'm checking first on the registry key that is not the one for the cluster and if the value is not there I’ll read the cluster one, that way we’ll get the value always.

The Keys are (remember Instance aware):

For Data: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer
and Value DefaultData

For Log: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer

and Value DefaultLog.

So now here’s the code.

declare @datadir nvarchar(4000)
,@logdir nvarchar(4000);

EXEC master.dbo.xp_instance_regread
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultData'
, @datadir output;

IF @datadir IS NULL
EXEC master.dbo.xp_instance_regread
, N'Software\Microsoft\MSSQLServer\Setup'
, N'SQLDataRoot'
, @datadir output;
EXEC master.dbo.xp_instance_regread
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultLog'
, @logdir output;

SELECT @datadir as Data, ISNULL(@logdir,@datadir) as Log;

Hope this helps you!

Kind Regards,
The Developer Seer.

1 comment:

  1. I guess it only holds true if the instance is a default instance.
    In case of a named instance we would have to use the location:

    and the values to fetch should be "DefaultData" and "DefaultLog"