31 Posts of using Sql Server with Powershell — Post 4: Server Object

For the 4th post, we’re going to get go into managing Sql Server itself rather than the data.  For that, we’re going to need a new object, the Server object.  In this example, we’re using the ServerConnection object to connect to the Server object.  We then go through all the databases on the server and print out the name of the databases:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $Server = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

    foreach($database in $Server.Databases){
        $database.Name
    }

    $srvConn.Disconnect();

}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

Lets step it up a bit and work on more than one server at a time.  Create a text file named Servers.txt and put it on the root of your c:\ drive.  In said file, put the name of a few servers that you want to query:

If you don’t have more than one server, just go into your ConfigurationManager in windows and add a few alias’ in that point to your local instance (that’s what I did…).  Make sure to add them to both the 32-bit and 64-bit Sql Native Client alias’.

So, let’s iterate through all databases on all our servers:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    get-content -Path C:\Servers.txt | %{
        $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $_
        $Server = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

        Write-Host $_ -ForegroundColor Red

        foreach($database in $Server.Databases | where{!$_.IsSystemObject}){
            "Database name is:  $($database.Name)";
        }

        $srvConn.Disconnect();
    }

}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

The ‘%’ is an alias for ‘foreach-object’ for the list of servers in the text file.  The ‘$_’ is just a reference to the current item in the list of servers in the text file.

• Use Get-Content to retrieve the contents of our c:\Servers.txt file.
• Iterate through the servers in the file, each time changing the server name ($_) in the ServerConnection object.
• Filter using the where-object to only show non-system databases (where{!$_.IsSystemObject})

The $database.name is surrounded by $() so it can evaluate the database name.  If it weren’t there, the string would print out the database name with .Name after it, as shown below:

Even though I’m using a ServerConnection object to connect via the Server object, you don’t have to do this.  You can just as easily pass the servername string into the Server object and it will work just fine.

$Server = new-object Microsoft.SqlServer.Management.smo.Server 'bacon'

So, the server object is the object you’ll most likely be using the most when administering the server.  Under the server object, you’ll find logins, databases, server roles, the sql agent job server, etc…

In upcoming posts, we’ll go through examples of adminstering the most common objects  that hang off the Server object.