In the previous article, we saw how to connect to a SQL server and retrieve information using PowerShell.
Consider the case when you have hundreds of servers and wish to retrieve data from all of them. Will you manually perform the query against each server? Nope! All you have to do is create a basic loop for each server and run your query. It will connect to each server and collect data automatically.

Let’s start.
First, let’s have a look how a loop works in the Powershell. Here we will use ‘foreach’ loop.

Foreach (placeholder variable IN the collection)
{
Code for what you want to do in the collection
}

Now, if you have hundred servers, put them into a text file and read into a powershell variable.
$servers = get-content -Path “C:\temp\Serverlist.txt”

Now you have to put a foreach loop to access each server you stored in $servers

foreach ($server in $servers)
{
Your code
}

Here is your code:

<#
   Powershell script to connect multiple servers
#>

#Read the server list
$servers = get-content -Path "c:\temp\Serverlist.txt"

foreach($server in $servers)
{
   #Create a connection to the server
   $connectionstring = "Data Source"="+"$server"+";Initial Catalog=master;Integrated Security=SSPI;"
   $connection = New-Object System.Data.SqlClient.SqlConnection
   $connection.ConnectionString = $connectionstring

   #Create a command
   $command = New-Object System.Data.SqlClient.SqlCommand
   $command.CommandText = "Select * FROM sys.databases"

   #Assign the command to the connection
   $command.Connection = #Connection

   #Create an adapter
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $SqlAdapter.SelectCommand = $command

   #Create your dataset and fill the result
   $DataSet = New-Object System.Data.DataSet
   $SqlAdapter.Fill($DataSet)
   $DataSet.Tables[0] | Export-csv "C:\temp\output.csv" -append -Force -NoTypeInformation #Output your 
   data to a csv file
}

You have observed in the Export-csv command we have added -append in the end. This will append your data into the csv file retrieved from each server.

-NoTypeInformation property will not put default csv information at the top of your csv file.

Try this script and see if it’s helpful to you. Happy Learning!