Check If Database Exists Using Powershell

For most DBA’s we will use SSMS and connect to the SQL instance to see if a databases exists and is spelled correctly. This is usually in response to troubleshooting a connection so connecting to the SQL instance makes sense as you may need to check the login permissions too.

Get-SqlDatabase

I found this Powershell command that will tell you if a databases exists,.. and it’s only one line too.

Get-SqlDatabase -ServerInstance "Server\sqlinstance" -Name "MyDBName"

The trouble with this command is it fails if the database is not found. We could wrap it in a Try/Catch but I would rather just get my answer in something more readable.

Using T-SQL Instead

I decided to use our Invoke-Sqlcmd command and use some T-SQL to check for multiple databases as well as partial names too. The first part of our script is to define a variable for the SQL instance and then we need an array so the Admin can enter multiple databases to check.

You will also notice one of the array members has the % symbol. Were going to use the ‘LIKE’ T-SQL command so we can use the exact DB names and partials too.

$instance = 'server\sql'
$dbarray = @( 'master', 'chicken123', 'Blue%')

Now that we have our array of databases to look for we will need Foreach statement to go through each one, check if it exists in SQL and return some kind of response.

The $db parameter is used to create our SELECT statement and we save the results to the $result variable.

Foreach ($db in $dbarray) {
    $dbquery = "SELECT name FROM master.sys.databases WHERE name LIKE '" +$db+ "'"
    
$result = Invoke-Sqlcmd -ServerInstance $instance -query $dbquery

if ($result -eq $null) { 
    Write-Host "[$db] NOT found" }
else { Write-Host "[$db] Exists!" } 
}

The final section is an IF statement that returns our human readable message for each database. In this script the Invoke-Sqlcmd command is connecting to SQL and then running one statement for each array entry we added. In theory we could enter as many databases as we want to.