Creating SQL Server reports using PowerShell

For one of my project we had to daily run some scripts on CMS and copy paste that information into an Excel sheet and then send it across to the different stakeholders providing them the status of current health of the environment.

This being manual process was error prone and time consuming. To overcome this I created a simple PowerShell script to generate the report into some common location. Over here I’ll give you a quick overview how you can also do this in your environment.

Prerequisites

1. SQL Server module
2. ImportExcel module

Once you have installed the above two modules create a csv file with the name of all your SQL Server instance and change the -Path on line one to your csv location.

$servers = Import-Csv -Path 'C:\Servers\serversnp.csv'
 
Foreach ($server in $servers)
{
 
#Database status for all the servers
 
$statusquery = "Select name,state_desc from sys.databases where state_desc != 'ONLINE'"
 
Invoke-Sqlcmd  -ServerInstance $server.servers -Query $statusquery | Select-Object name,state_desc| Export-Excel -Path 'C:\Report\dailytest.xlsx' -WorksheetName Sheet3 -Append -TableName Database_status3 -AutoSize
 
}

Github link.

It will create a Excel sheet with the name dailytest.xlsx and having a table like below.

I have masked the server and database name