Run a SQL Query on multiple servers and save output to excel using power shell (Part1)You will see how much is powershell is useful for DBA after using this script.

Run a SQL Query on multiple servers and save output to excel using power shell (Part1)
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:\Users\homarxm\Desktop\BackupHistory\templet\serverlist.txt

AUZUR2K8DC6DE\JIRA2DEV AUZUR2K8DC6DF\JIRA2TST AUZUR2K8DC6DQ\JIRA2PROD AUZUR2K3DC4Q03 AUZUR2K3DC4L03 AUZUR2K3DC5J AUZUR2K3DC6UA AUZUR2K3DC6UA\PQUANT_TEST AUZUR2K3DC6UA AUZUR2K8DC6DD

Step 2:Created a folder called scripts in my C: then Copy the below script into another text file and save it as
C:\Users\homarxm\Desktop\BackupHistory\templet\script.ps1

# Script Runs on servers listed in serverlist.txt # use sqlps.exe to run this script # In cmd, sqlps.exe C:\Users\homarxm\Desktop\BackupHistory\templet\script.ps1   $query = " select  DISTINCT l.name LoginName, l.type_desc, l.is_disabled ,@@servername FROM sys.server_principals l  WHERE  l.type_desc <> 'SERVER_ROLE' and   l.type_desc in ('WINDOWS_LOGIN','SQL_LOGIN') ORDER BY 1"   #Path to the excel file to be saved.   $csvFilePath = "C:\Users\homarxm\Desktop\BackupHistory\templet\queryresults.csv" $excelFilePath = "C:\Users\homarxm\Desktop\BackupHistory\templet\queryresults.xls"   # Run Query against multiple servers, combine results # Replace "Server1", "Server2\INSTANCE1" with names of your SQL Server instances   $instanceNameList = get-content C:\Users\homarxm\Desktop\BackupHistory\templet\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.