Default SqlServer database creation path

Default SqlServer database creation path

• Joe.M

  it return empty result set... im not set specific path for data/log when installing sql2005.
  
  

• Abu-Bakr

  If you have the default location for your database files, the script will not return anything.
  Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
  To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.
  
  

• alejoespinosa

  Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:
  
  master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
  
  But fails for
  
  master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures', N'Xp_regread&Allowed&Paths', @SmoDefaultFile OUTPUT
  
  it also fails if you try Xp_regread Allowed Paths
  or Xp_regread%Allowed%Paths
  
  Any ideas
  
  

• ram alisam

  The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.

  The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:

  HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot

  and

  HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath

  The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...

  If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.

  You can find the registry key for your SQL 2005 instance here:

  HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>

  ...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>

  Now go to:

  HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup

  to get SQLDataRoot and SQLPath...

  then go to:

  HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer

  to get DefaultData and DefaultLog...

  Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.

  
  
  

• Rich Tebb

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=965821&SiteID=1
  

• tpkx

  Here you are, courtesy to SQL Profiler and SSMS
  
  
  

  declare @SmoDefaultFile nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT

  declare @SmoDefaultLog nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT

  SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]

  
  
  
• Default SqlServer database creation path