7

How can I find the SQL server name AND instance where my databases are stored using PowerShell? Assuming I am not using an alias and can't check cliconfg.

In servers in farm, I can see the server name but no the instance. I have the same issue if I run 

Get-SPServer  | select name

If I select any of the other properties (role, serviceinstances, etc.) only the name gets displayed.

Another close one was the cmdlet Get-SPDatabase with parameter -ServerInstance.. but I have to know the server instance.

I found this block of C# code, but I'm not sure how to run it. Can someone explain how I can run the C# code to get instance name?

Improve this question

1 Answer 1

Reset to default
12

If you use the commandlet Get-SPDatabase there are several properties of the returned object that can give you the information you need.

$db = (Get-SPDatabase)[0]
$db.Server

Will print out the full server name, and I would assume instance, for that database. Another interesting property would be 

$db.DatabaseConnectionString

That will print out something like

Data Source=SP2013;Initial Catalog=SP2013_State_Service_DB;Integrated Security=True;Enlist=False;Pooling=True;Min Pool Size=0;Max Pool Size=100;Connect Timeout=15

Data Source will contain the full server and instance name as well. I'll convert that code for you and add it as an edit.

Here is the code on the link you gave converted to PoSh. I have not wrapped it in a function as I am not sure how you might want to use it...

if ( (Get-PSSnapin -Name microsoft.sharepoint.powershell -ErrorAction SilentlyContinue) -eq $null )
{
    Add-PSSnapin microsoft.sharepoint.powershell
}
Start-SPAssignment -Global

    $farm = Get-SPFarm

    foreach ($server in $farm.Servers)
    {
        foreach ($svc in $server.ServiceInstances)
        {
            if($svc -is [Microsoft.SharePoint.Administration.SPDatabaseServiceInstance])
            {
                $s = [Microsoft.SharePoint.Administration.SPDatabaseServiceInstance]$svc;
                if([System.string]::IsNullOrEmpty($s.Instance))
                {
                    $svc.DisplayName
                }
                else
                { 
                    $svc.DispalyName + "\" + $s.Instance
                }
            }

        }
    }


Stop-SPAssignment -Global
Improve this answer
4
  • This is great. Both do the job. I find that you don't need this in the script code$sp = Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.dll"code
    – spex5
    Apr 25, 2014 at 18:53
  • 3
    In mean time I was writing the same script with pipes :). Copy-Paste this in SharePoint Management Shell: Get-SPFarm | foreach { $_.Servers | ForEach-Object { $a = $_; $_.ServiceInstances | foreach { if ([Microsoft.SharePoint.Administration.SPServiceInstance]$_ -is [Microsoft.SharePoint.Administration.SPDatabaseServiceInstance]) { Write-Host $a; Write-Host $_.DisplayName }}}}
    – Naim Murati
    Apr 25, 2014 at 18:57
  • Awesome, I'll update my answer to reflect that.
    – Robert Kaucher
    Apr 25, 2014 at 18:59
  • Thanks, the .DatabaseConnectionString got me the correct info (SQLInstance name) where .Server only returns the SQLServer. +1'd
    – BenCes
    Aug 25, 2015 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.