Check SQL Server Database Status

It's been a month since my last post. Today I am posting a function that can be used to check the status of MS SQL Server databases. At the current moment, I am not sure about the limitations on where this script works like which versions of Windows OS or supported MS SQL Server versions. I have been using the script on my laptop where I have installed Windows 10 OS and SQL Server 2014 Developer Edition. SQL Server Developer edition can be downloaded from here.

There are multiple methods to connect to a SQL Server instance from Powershell. I found this very useful article on sqlshack.

The script in this post makes use of the SQLPS module.

<#

.Synopsis

Check database status on MS SQL Server instances.

.DESCRIPTION

Check the database status on one or a list of MS SQL Server instances.

.Parameter ServerInstance

Provide the server instance to connect to in case of a named instance servername\instancename.

.EXAMPLE

Get-DBStatus -ServerInstance localhost

PS C:\Users\rages>Get-DBStatus -ServerInstance localhost

db_id name user_access state recovery_model

----- ---- ----------- ----- --------------

1 master MULTI_USER ONLINE SIMPLE

2 tempdb MULTI_USER ONLINE SIMPLE

3 model MULTI_USER ONLINE FULL

4 msdb MULTI_USER ONLINE SIMPLE

5 ReportServer MULTI_USER ONLINE FULL

6 ReportServerTempDB MULTI_USER ONLINE SIMPLE

7 AdventureWorksLT MULTI_USER ONLINE SIMPLE

8 AdventureWorks2014 MULTI_USER ONLINE SIMPLE

#>

function Get-DBStatus{

[cmdletBinding()]

param

(

[Parameter(Mandatory = $True, Position = 0)]

[string[]]$ServerInstance

)

Clear-Host

Push-Location

Import-Module sqlps -DisableNameChecking

Pop-Location # Switching back to original PSDrive

$query = "SELECT

db.database_id as db_id,

db.name,

db.user_access_desc as user_access,

db.state_desc as state,

db.recovery_model_desc as recovery_model

FROM sys.databases as db"

foreach ($instance in $ServerInstance)

{

$instance

Write-Host '-------------------------->>'

$resultset = Invoke-Sqlcmd -ServerInstance $instance -Database master -Query $query

$resultset | Format-Table -AutoSize

}

}

The script is optimized to view the result in console. Remove the Format-Table part if anyone want to store the result set into variable. Use of Push-location and Pop-location commandlets make sure that after the functions is called we get back to the same PS drive before calling the function and go with different PowerShell commands which are not part of SQLPS module. A shortcoming on the script is that there is no authentication mechanism currently added to the script.