Include more servers in Powershell scripts

Question

I m new to Powershell and I need guidance on this Powershell scripts below. I m working in multi tenant environment. I need to execute a scripts on 15 servers and every servers have around 20 databases. This scripts help me a lot to execute a scripts in all database and output the results for every single database. I need to include more servers in $instancelist but when I add more servers it does not work. I need help to include more servers and this scripts must execute in all servers in $instancelist and the the output file must have server name, currently the output file have $path name, database name.log, it must have server name if I m able to include more servers.

One more thing, can I include BCP in this powershell scripts to output the results since it will come out with millions of rows

$InstanceList = "DESKTOP-6U9IKQD" $filepath = "C:\b1\script1.sql" $path = "C:\b1\jack"

$databases = invoke-sqlcmd -ServerInstance $InstanceList -Database "master" -Query "select name from sys.databases where name like 'adventureworks%'"

foreach ($database in $databases) { $DBname = $database.name # Outputs one file per database $outfile = $path + "."+$database.name+".log" #Execute scripts Invoke-Sqlcmd -ServerInstance ${InstanceList} -Database $database.name -InputFIle $filepath | out-file -filepath $outfile

$database.name
}

Answer 1

You need to change this line (#13 in my previous answer) from this:

Invoke-Sqlcmd -ServerInstance ${InstanceList} -Database $database.name -InputFIle $filepath | 

To this:

Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath | 

Sorry I missed that in my reply, :-(

Answer 2

This might be what you're looking for:

$InstanceList = "DESKTOP-6U9IKQD", "DESKTOP-another", "DESKTOP-andonemore"
$filepath = "C:\b1\script1.sql" 
$basename = "C:\b1\jack"

$InstanceList |
    ForEach-Object{
        $databases = invoke-sqlcmd -ServerInstance $_ -Database "master" -Query "select name from sys.databases where name like 'adventureworks%'"

        foreach ($database in $databases) {
            # Outputs one file per database
            $outfile = $basename, $database.name, "log" -join "."
            #Execute scripts 
            Invoke-Sqlcmd -ServerInstance ${InstanceList} -Database $database.name -InputFIle $filepath | 
                out-file -filepath $outfile

            $database.name
        }
}

BCP is just a command line executable so you can run it from your script.

Answer 3

Still the same error