SQL Server Performance

How to query the default data and log file location

Discussion in 'SQL Server 2005 General DBA Questions' started by shabnyc, Jan 25, 2008.

  1. shabnyc Member

    Hi all, I am trying to query the default location of data and log files in a SQL server instance through an SP. can anyone help in providing the T-SQL for that. I used the following but it didnt work on my PC. is there any other way to do it. any help is appreciated
    declare @SmoDefaultFile nvarchar(512)
    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT

    declare @SmoDefaultLog nvarchar(512)
    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT

    SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]
  2. ndinakar Member

    Have you tried sp_helpdb 'dbname' ?
  3. shabnyc Member

    Thanks nDinakae, this SP wouldnt help me, I need to get just the default data and log dir for an instance, not the files for a database. For instance if you set the default dir when installing SQL server instance to N:DB_filesData and N:DB_filesLogs, the tsql am trying to use will return these 2 directories only so I can use them in another Tsql.
  4. Buveta New Member

    Maybe using this
    Declare @SmoDefaultFile nvarchar(512)
    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
    Declare @SmoDefaultLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT
    if the variable is null the default configuration for the data and log file is empty.
  5. shabnyc Member

    Thanks Buveta. the query you provided succeeds but does not show output in Management studio. do I have to set anything before using it?
  6. Buveta New Member

    Sorry, my script is incomplete
    declare @SmoDefaultFile nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N
    'DefaultData', @SmoDefaultFile OUTPUT
    declare @SmoDefaultLog nvarchar(512)exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N
    'DefaultLog', @SmoDefaultLog OUTPUTselect
    @SmoDefaultFile as MdfDefaultLocation, @SmoDefaultLog as LogDefaultLocation
  7. Buveta New Member

    I tesst this script and if the default database location on SQL Server Instance Properties (TAB: Database Settings) is empty the result is null. but in my pc the result is OK.
  8. shabnyc Member

    Thanks Buveta, that helps a lot. it is working in my PC. Now I have to use .bat file to get the results from what you provided above and restore a DB from a known .bak file and restore to the above data/log directory using OSQL. am trying but couldnt get it right.
    thanks again

Share This Page