Below script will query registry settings to return folder informations (SQL Root, Data, Log and Backup).

I use this script to determine the default locations, put it in variables an use it for creating databases or backups.


DECLARE @SQLDataRoot nvarchar(512)
DECLARE @DefaultData nvarchar(512)
DECLARE @DefaultLog nvarchar(512)
DECLARE @BackupDirectory nvarchar(512)

-- check defaults
-- installation directory

-- sql server root
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\Setup',
N'SQLDataRoot',
@SQLDataRoot OUTPUT
-- 'data' directory
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@DefaultData OUTPUT
-- 'log' directory
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@DefaultLog OUTPUT
-- 'backup' directory
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@BackupDirectory OUTPUT

-- default locations set?
IF LEFT(REVERSE(@SQLDataRoot), 1) <> N'\'
SET @SQLDataRoot = @SQLDataRoot + N'\'
-- regkey = 0 then default root
SET @DefaultData = ISNULL(@DefaultData, @SQLDataRoot + 'DATA')
SET @DefaultLog = ISNULL(@DefaultLog, @SQLDataRoot + 'DATA')

--
PRINT '''Data'' - Directory : ' + @DefaultData
PRINT '''Log'' - Directory : ' + @DefaultLog
PRINT '''Backup'' - Directory : ' + @BackupDirectory


CU
tosc