Powershell script to report SQL Database Status

Question: I need a  SQL script  to check database status on all  SQL Server instances. The script will be used as part of a reboot cycle.  There are different reasons for a reboot cycle, such as patching, DR testing or service management.  

We have an alerting system in place. The alerting system is based on an exception based reporting method.

 The report should record the status of every database. I could also add the report to the service desk as a post test results.

Answer:  Checking through every SQL Server instance and database is straightforward. All the information exists within the SQL Server.  The main challenge is to iterate through every SQL Server and return the results as one report.

It is possible you may not be able to complete a SQL Server connection. You need to build in some sort of alert to report these connection exceptions.

The method outlined , is just one method amongst many. This method utilises Powershell . The Powershell script iterates through a list of SQL Server Instances, listing the databases and status.   Ensure the login executing the script has enough permissions to connect and read the sys.databases view.

This method doesn’t use Linked Servers. But I’ve worked in various environments using a centralised server with multiple Linked Servers. There are plenty of advantages utilising a centralised server – linked server system. But I find the flexibility of Powershell overrides a strict SQL Server only solution.

Step 1 – SQL code to check database status

Read SQL Database Status with sys.databases  for a description of the various database status levels.

This code snippet returns the SQL Server name, database name and database status. Depending on your requirements , you may want to return only the exceptions . i.e databases with a status not equal to ONLINE.

SELECT @@servername,name, state_desc as Database_status
FROM sys.databases

Step 2 – Powershell to iterate through SQL Server instances

The Powershell script iterates through the list of SQL Server instances , generates a list and creates a HTML report.

The "instances_prod.txt" file should have some SQL Server Instances listed such as:

myserver1\instance2

myserver2\instance1

myserver3\instance4

#Script created by Jack Vamvas  www.sqlserver-dba.com
$isodate=Get-Date -format s 
  $isodate=$isodate -replace(":","")
  $basepath=(Get-Location -PSProvider FileSystem).ProviderPath
  $outputfile="\database_status_" + $isodate + ".html"
  $outputfilefull = $basepath + $outputfile

 $style="" $dt = new-object "System.Data.DataTable" 
foreach ($svr in get-content "C:\powershell_scripts\instances_prod.txt")
{ $svr $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi" 
       $cn.Open() 
      $sql = $cn.CreateCommand() 
      $sql.CommandText = " SELECT @@servername,name, state_desc as Database_status FROM sys.databases " 
      $rdr = $sql.ExecuteReader() $dt.Load($rdr) $cn.Close()
 }
 $dt| select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $style -body "SQL Server Database Status Report" | Set-Content $outputfilefull

Read More on Powershell

Expand your Powershell mind – Three key cmdlets - SQL Server DBA

Powershell – setting the Execution Policy - SQL Server DBA

Powershell Scripts for DBA - SQL Server DBA