Get SQL Server Instance Name Using PowerShell | Details

We can use a commandlet called get-sqlinstance to quickly query an existing SQL instance on a remote server for more information. The administrator often needs to inventory the server environment and that includes identifying the SQL servers as well as all of the instances.

Another useful commandlet for SQL Server is the Get-SQLDatabase that includes more detailed data on the databases hosted.

Here at DB Tales we tend to use the Invoke-SQLCmd commandlet as you can easily use one of your existing scripts to return specialized data. This post is about the Get-SQLInstance commandlet so I add this here just for reference.

Invoke-sqlcmd -ServerInstance "MyServer01\TestSQL01" -Query "SELECT @@version"

Get-SQLInstance Example

The commandlet is provided in the “SQLServer” module so you may need to import this module in order to use it.

import-module sqlserver   ## may need to import

get-sqlinstance -ServerInstance "MyServer01\TestSQL01" 

This command will bring back the basic details for a given instance such as the SQL Version, product level and platform. A better usage for this commandlet is to use a foreach and a list of SQL Server Instances from a query or an array.

$SQLservers = @( 'MyServer01', 'SQLServer02\mysql02', 'AnotherServer03\sql03' )

foreach($singlesql in $SQLservers) {
    get-sqlinstance -ServerInstance $singlesql
}

Multiple Instances – “failed to connect to server”

I didn’t find any answers to this so I wanted to include here, The documentation for get-sqlinstance states:

“The Get-SqlInstance cmdlet gets a SQL Instance object for each instance of SQL Server that is present on the target computer. If the name of the instance of SQL Server is provided, the cmdlet will only get this specific instance of SQL Server.”

I was not able to get this to work on a remote server that has ‘multiple instances installed’, i’m not sure why. If you specify an instance it works fine but it does not return all “that is present” on the target server.

See below for a work around…

One Instance Example

When we assign the result to a variable we can pull more information about the instance.

$instance = get-sqlinstance -ServerInstance 'MySQLServer08\INS008'

$instance.Databases

($instance.Databases).Name

$instance.Databases.Count

$instance.UpdateLevel

$instance.BackupDirectory

$instance.Information

$instance.Information.BuildClrVersionString

$instance.Information.DatabaseEngineEdition

($instance.Information).OSVersion

$instance.Information.ErrorLogPath

SQL Server Configuration

With the instance object assigned to a variable we can gain access to more information on the SQL instance configuration settings.

$instance = get-sqlinstance -ServerInstance 'MyServer02\SQL07'

$instance.Configuration.Properties

$instance.Configuration.XPCmdShellEnabled.RunValue

$instance.Configuration.XPCmdShellEnabled.ConfigValue

$instance.Configuration.MaxServerMemory.Maximum

$instance.Configuration.IsSqlClrEnabled.RunValue

$instance.Configuration.UserConnections.Maximum

$instance.Configuration.Locks.RunValue

$instance.Configuration.RemoteAccess.RunValue

Get SQL Instances & More

We can use the Get-Service commandlet to grab all of the SQL Services Installed when there are more than one installed on a single server.

Note: This works for named instances on a standalone server but not clusters, you also will need to modify to include a default instance if installed too.

We are grabbing the service and removing the first part part of the service name to give us an instance list and then using that for our get-sqlinstance commandlet.

1. Grab the running SQL services on one machine and split the string
2. Filter the ‘MSSQL’ entries in the array so we only have the instance names
3. Connect to each instance using Get-Instance

$server = "SomeSQLServer05"

## 1.
$sqlservices = (Get-service -ComputerName $server -Name MSSQL$* | Where-Object { $_.Status -eq "Running" }).Name.Split("$")

## 2.
$instances = $sqlservices | Where-Object { $_ -ne "MSSQL" }

## 3.
foreach ($name in $instances) {

    $finalinstance = $server+"\"+$name 

    get-sqlinstance -ServerInstance $finalinstance
}

---