Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Powershell script to report SQL Database Status

30 March,2015 by Tom Collins

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:






#Script created by Jack Vamvas
$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"
$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


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Powershell script to report SQL Database Status | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer