Below is the scripts I m using to loop the servers and databases in multiple servers. One server will have more than 10 databases and I m using this PS scripts to generate the information and output to CSV but I m having high memory utilization when using this PS scripts. Did anyone know how to use sqlcmd include in this PS scripts because I believe generate data using sqlcmd and output to csv will have less memory utilization
$InstanceList = "DESKTOP-6U9IKQD", "DESKTOP-6U9IKQD\BM"
$filepath = "C:\b1\script1.sql"
$Path = "C:\b1\Server"
$InstanceList | ForEach-Object{
$databases = invoke-sqlcmd -ServerInstance $_ -Database "master" -Query "select name from sys.databases where name like 'adventureworks%'"
foreach ($database in $databases) {
$outfile = $Path,$_.replace("\","_")+"txt" -join "."
"Database name: $($database.name)" | Out-File -FilePath $outfile -Append
Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath | Out-File -FilePath $outfile -Append
}
}