Powershell to execute sql script on all server

Hi folks, I was working on some maintenance activity where i have to execute one sql script on all sql server in domain and collect the result on csv file.
So I created one txt file in which i kept all server list. I also put sql script on same folder. then i wrote below script on powershell. I hope it will help you too.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$Serverlist= Get-Content "D:\Lab\PowerShellTest\AllServerScript\ServerList.txt"
$SqlScript= "D:\Lab\PowerShellTest\AllServerScript\Script.sql"
$Outputfile= "D:\Lab\PowerShellTest\AllServerScript\Report_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).csv"
$Logfile= "D:\Lab\PowerShellTest\AllServerScript\Log_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).txt"

#=====================
#Sql script to execute
#=====================
@(
ForEach($ServerName in $Serverlist)
{
try {
write-host $ServerName
Invoke-Sqlcmd -InputFile $SqlScript -serverinstance $ServerName -Database master}
Catch {

$ServerName | Out-File $Logfile -Append
$_ | Out-File $Logfile -Append
}}
) | export-csv -NoTypeInformation -Path $Outputfile


If you want to learn PowerShell, please follow below link-

Learn PowerShell with Vimal Lohani

No comments:

Post a Comment