How to run a SQL Query on list of servers and save output to excel using power shell?

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=""></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

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.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s