1

Requirement : My requirement is I have to list the available databases from 150 servers. Each server has minimum 1 and maximum 15 instances.

Below script is working only for instances listed in sqlserver.txt but I need to fetch multiple instances across multiple servers.

Help is highly appriciated.

ForEach ($instance in Get-Content "C:\PowerSQL\SQL_Servers.txt") 
{ 
    Import-Module SQLPS -DisableNameChecking 
    Invoke-SQLcmd -Server $instance -Database master 'select @@servername as InstanceName,name as DatabaseName,state_desc as DBStatus from sys.databases' | Format-Table       
}
2
  • You don't need to import SQLPS in every iteration. Commented Sep 13, 2016 at 12:59
  • Many Thanks Pawel for reply. Can you please help me with my requirement. Commented Sep 13, 2016 at 13:05

2 Answers 2

3

You can use this script to find all reachable instances on your network and running your query there:

Import-Module SQLPS -DisableNameChecking 
$servers = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() 

ForEach ($i in $servers) { 
    $instance = $i.ServerName+"\"+$i.InstanceName
    Invoke-SQLcmd -Server $instance -Database master 'select @@servername as InstanceName,name as DatabaseName,state_desc as DBStatus from sys.databases' | Format-Table       
}

If you need only server name to pass then use $instance = $i.ServerName. Part of code was taken from here long time ago.

EDIT

With writing in CSV file and error catching:

Import-Module SQLPS -DisableNameChecking 
$servers = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() 
$results = @()
ForEach ($i in $servers) { 
    $instance = $i.ServerName+"\"+$i.InstanceName

    try {
        $sqlres = Invoke-SQLcmd -Server $instance -Database master 'select @@servername as InstanceName,name as DatabaseName,state_desc as DBStatus from sys.databases'

        ForEach($st in $sqlres) {
           $instanceinfo = @{            
                    InstanceName = $st.InstanceName
                    DatabaseName = $st.DatabaseName                 
                    DBStatus     = $st.DBStatus
            } 
            $results += New-Object PSObject -Property $instanceinfo     
        }
    } catch {
        "error when running Invoke-SQLcmd "+$instance
        Write-Host($error)
    }
}

$results | export-csv -Path D:\sql_instances_info.csv -NoTypeInformation
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks G0fr1 its working to list all the instances.If you don't mind can you help me to keep the log.txt to keep failed instance or stopped instance details. It would be great if we can keep the results into csv or excel file.
I add writing to CSV and error catching, should work fine. It will run Invoke-SQLcmd in the loop and collect data. Then write in D:\sql_instances_info.csv file
thats a typo here$st.DBStatusv - my bad, changed to $st.DBStatus
My pleasure! :)
0

Im not sure what is the problem here. You can put all servers/instances in txt file and iterate:

#array of addresses, this can be fetched from file
$list = "localhost\SQL2014",".\SQL2014","(local)\SQL2014" #MyServer\MyInstance

$list | `
  % { Invoke-Sqlcmd -Server $_ -Database master 'select @@servername as InstanceName,name as DatabaseName,state_desc as DBStatus from sys.databases' } | `
  Format-Table -AutoSize

If those are remote servers without integrated security you would need to pass -UserName and -Password arguments.

2 Comments

Pawel , I would like to pass only server name instead of instance names.If I specify instance names then its working fine otherwise its taking default instance only and skipping the named instances.Also I would like to keep the log if possible to store the failed attempts.
Thanks Pawel for your support.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.