Default Data Directory

• Ferry Huang

  Old Hand

  Points: 365

  More actions

  December 19, 2006 at 7:44 pm

  #92056

  Greetings all ...

  I need to get the value from Default data directory and Default log directory in Database Settings tab in SQL Server Properties. How can i do this?

  Thanks in advance.

  Regards,

  Ferry

  ---

  Regards,

  Ferry Huang

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  December 19, 2006 at 8:20 pm

  #679035

  This information is stored in the registry. Have a look at

  HKLM\Software\Microsoft\MSSQLServer\MSSQLServer

  If there is a non-default value for the log/data files, you will see an entry for "DefaultData" and "DefaultLog"

  If there defaults are in use they will not be there.

  Enterprise Manager uses the following commands to read these registry keys

  xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog'

  xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData'

  If there are no defaults you may get an error similar to the following:

  Msg 22001, Level 1, State 22001

  RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'

  Hope this is what you needed

  Cheers

  Stephen

• Ferry Huang

  Old Hand

  Points: 365

  More actions

  December 19, 2006 at 8:30 pm

  #679036

  Thanks a lot Stephen, yes, this is what i need.

  But when the Default data directory is not set, how to handle this since it will generate an error?

  And when the Default data directory is not set, it should return the path which is default path when we first time install the server. Can this be done?

  ---

  Regards,

  Ferry Huang

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  December 19, 2006 at 9:00 pm

  #679038

  I think that this is in

  HKLM\Software\MSSQLServer\Setup

  The key SQLDataRoot is probably the one to go with.

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  December 19, 2006 at 10:12 pm

  #679046

  Error handling for calls to extended sprocs isn't that easy.

  You might have to make some assumptions. The only thing of use I could dig up was the following code snippet (from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=154842&SiteId=1) which might help you actually using the results

  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]

• Ferry Huang

  Old Hand

  Points: 365

  More actions

  December 20, 2006 at 2:50 am

  #679069

  Thank you very much for your help Stephen

  ---

  Regards,

  Ferry Huang

• Robert Davis

  One Orange Chip

  Points: 28027

  More actions

  December 21, 2006 at 1:21 pm

  #679537

  Here's a script I wrote after a service pack installation deleted all of our default file locations. It is for SQL 2005, so it may need to be modified to work on SQL 2000. It uses xp_instance_regread to check the default file locations and if not set, it uses xp_instance_regwrite to write them to the registry.

   

  Declare

  @DataDir nvarchar(4000),

  @LogDir

  nvarchar(4000)

  Exec

  xp_instance_regread N'HKEY_LOCAL_MACHINE',

  N

  'Software\Microsoft\MSSQLServer\MSSQLServer',

  N

  'DefaultData',

  @DataDir

  output,

  'no_output'

  Exec

  xp_instance_regread N'HKEY_LOCAL_MACHINE',

  N

  'Software\Microsoft\MSSQLServer\MSSQLServer',

  N

  'DefaultLog',

  @LogDir

  output,

  'no_output'

  Select

  @DataDir, @LogDir

  If

  IsNull(@DataDir, '') <> 'D:\SQL_DATA'

  Begin

  Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQL_DATA'

  End

  If

  IsNull(@LogDir, '') <> 'D:\SQL_LOG'

  Begin

  Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\SQL_LOG'

  End

  Exec

  xp_instance_regread N'HKEY_LOCAL_MACHINE',

  N

  'Software\Microsoft\MSSQLServer\MSSQLServer',

  N

  'DefaultData',

  @DataDir

  output,

  'no_output'

  Exec

  xp_instance_regread N'HKEY_LOCAL_MACHINE',

  N

  'Software\Microsoft\MSSQLServer\MSSQLServer',

  N

  'DefaultLog',

  @LogDir

  output,

  'no_output'

  Select

  @DataDir, @LogDir

  ---

  My blog: SQL Soldier[/url]
  SQL Server Best Practices: SQL Server Best Practices
  Twitter: @SQLSoldier
  My book: Pro SQL Server 2008 Mirroring[/url]
  Microsoft Certified Master: SQL Server, Data Platform MVP
  Database Engineer at BlueMountain Capital Management[/url]