SMO Missing properties

  • Jeff Shurak

    SSCrazy

    Points: 2361

    I have a server that seems to be missing some properties when accessing via SMO in powershell, specifically the default data and log file locations. These can be viewed through ssms and tsql. Has anyone encountered this before?

  • Lowell

    SSC Guru

    Points: 323352

    thos locations are stored in the registry, and not in any table, or as a property of a server, as far as i know.

    ...how are you getting them via powershell?

    i get them via a TSQL like this:

    EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',

    N'BackupDirectory';

    EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultData';

    EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultLog';

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Jeff Shurak

    SSCrazy

    Points: 2361

    Lowell I can grab them that way and might if need be, but ideally in powershell. Its pretty straight forward in powershell

    $Instance = New-Object('Microsoft.SqlServer.Management.Smo.Server') $InstanceName

    $Instance.DefaultFile

    $Instance.DefaultLog

    These are blank for a specific instance,but populated for the majority of other instances.

  • Jeff Shurak

    SSCrazy

    Points: 2361

    It looks like it might be a registry issue. There don't appear to be DefaultFile and DefaultLog entries in the standard location. Thanks for pointing me down the right path.

  • tibileo

    Valued Member

    Points: 55

    You might want to try additional properties, because maybe the user does not have rights to VIEW SERVER STATE/DEFINITION .

    Or maybe the 2 properties are not being initialized because of the location of the user database files.

    Try the following alternatives:

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    $server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'localhost'

    $setting1 = $server.Settings.DefaultFile

    $setting2 = $server.Settings.DefaultLog

    $setting3 = $server.Information.MasterDBPath

    $setting4 = $server.Information.MasterDBLogPath

    $setting5 = $server.InstallDataDirectory

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply