You will see how much is powershell is useful for DBA after using this script.
Step 1: Create a text file with list of servers you want to run the script on and name it ‘serverlist’ to C:\serverlist.txt
Step 2: I have Created a folder called scripts in my C: then Copy the below script into another text file and save it as C:\Scripts\script.ps1
# Script Runs on servers listed in serverlist.txt # use sqlps.exe to run this script # In cmd, sqlps.exe c:\scripts\script.ps1 $query = " Your SQL Query" #Path to the excel file to be saved. $csvFilePath = "c:\Scripts\queryresults.csv" $excelFilePath = "c:\scripts\queryresults.xls" # Run Query against multiple servers, combine results # Replace "Server1", "Server2\INSTANCE1" with names of your SQL Server instances $instanceNameList = get-content c:\serverlist.txt $results=@() foreach($instanceName in $instanceNameList) { write-host "Executing query against server: " $instanceName $results += Invoke-Sqlcmd -Query $query -ServerInstance $instanceName } # Output to CSV write-host "Saving Query Results in CSV format..." $results | export-csv $csvFilePath -NoTypeInformation # Convert CSV file to Excel # Reference : <a href="http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa">http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa</a> write-host "Converting CSV output to Excel..." $excel = New-Object -ComObject excel.application $excel.visible = $False $excel.displayalerts=$False $workbook = $excel.Workbooks.Open($csvFilePath) $workSheet = $workbook.worksheets.Item(1) $resize = $workSheet.UsedRange $resize.EntireColumn.AutoFit() | Out-Null $xlExcel8 = 56 $workbook.SaveAs($excelFilePath,$xlExcel8) $workbook.Close() $excel.quit() $excel = $null write-host "Results are saved in Excel file: " $excelFilePath
Step3: Copy and paste the SQL Query into the above script where it says like this
$query = ” Your SQL Query to run against multiple servers”
Step 4: Open CMD and run the script like this
Sqlps.exe c:\scripts\script.ps1
The results from the SQL Query from each server will be combined and saved as queryresults.xls into your scripts folder.
Note :The script uses windows authentication to connect to the SQL servers.
Awesome script !
If I need to save the output in table of some specific server, how to do that?