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