How to run a SQL Query on list of servers and save output to excel using power shell?
October 3, 2012
Posted by on
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
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
$workbook = $excel.Workbooks.Open($csvFilePath)
$workSheet = $workbook.worksheets.Item(1)
$resize = $workSheet.UsedRange
$resize.EntireColumn.AutoFit() | Out-Null
$xlExcel8 = 56
$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
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.