#PSTip Validate is a database is available on a SQL instance

Note: This tip requires PowerShell 2.0 or above.

Sometimes we need to know if a particular DB is available on a given instance before going ahead and making any configuration changes. The below code snippet allows you to quickly check if a DB is available on a particular SQL Server instance.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME

if($server.Databases["DatabaseName"]) {
    $true
} else {
    $false
}

One thing to consider is that the state of the database will not be considered here. For example, if the database is ‘Offline’; the conditional will still return ‘True’ as the DB is available in the catalog. So, if we want to check if the DB is available and also accessible then we can use the following method. It returns a boolean value based on the database accessibility.

$server.Databases["DatabaseName"].IsAccessible

About the author: Hemanth C Damecharla

Hemanth is a SQL*Server DBA and a PowerShell enthusiast who loves to keep looking for new things to tinker around with. He maintains that he can write poetry. He blogs at http://sqlchow.wordpress.com and you can follow him on Twitter: @sqlchow

Related Posts

%d bloggers like this: