Return Default SQL Server Folder

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

Qui tacet, consentire videtur