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.

Excel sample output
I have masked the server and database name

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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