Powershell script to get list of databases on a server

At one of my clients I received an email from one of the IT Project Managers asking a simple question:

"Can you please let us know which databases reside on the server below, Server1?"

First thought in mind, well from what particular sql instance on that server? It was our general practice to install multiple instances on a server. But at that moment I was not even sure if that server has multiple instance, is it a stand alone sever or a node/virtual name of a cluster server, alwayson cluster etc...

But, I kept that thought to myself.

Now, I could launch SSMS, connect to the sql instance, query the sys.databases and get requested information.

But I don't know the instance name top of my head. So I would need to RDP into the server or look up the meta data somewhere else, like maybe a spreadsheet with database inventory, assuming its up to date.

Instead of that, I decide to launch the Powershell and issue this command:

Get-WmiObject -Query "select * from win32_service `

where PathName like '%%sqlservr.exe%%'" `

-ComputerName "Server1"

It has only one sql instance, great.

Then I issued the following command to grab the databases on that list and send him the results.

Get-SqlDatabase -ServerInstance Server1

OR - To search for a specific pattern in the database name:

Get-SqlDatabase -ServerInstance Server1 | 

Where-Object {$_.Name -like '*report*'}

That last command requires the SqlServer module loaded, which I already have in my powershell startup.

Import-Module -Name SqlServer

But to add bit more value to this blog, I decided to turn this into a small PowerShell script.

PS variable $server_name is where you specify the server  name where you would like to look up database names. If that value is not specified, it will use the local computer name.

try
{
    Import-Module -Name SqlServer -ErrorAction Stop

    $server_name   = "Server1" # SERVER/HOST NAME HERE  
    $database_name = "admin"   # NAME OF THE DATABASE YOU WOULD LIKE TO SEARCH OTHERWISE LEAVE THIS BLANK
    $exact_match   = "N"       # WHETHER TO SEARCH FOR AN EXACT DATABASE NAME

    $logfile = "$env:TEMP\logfile_" + (Get-Date).toString("yyyyMMdd_HHmmss") + ".txt"
    
    "Start Time: " + (Get-Date)  | Out-File -Append $logFile
    "Log: $logfile"  | Out-File -Append $logFile
    ""  | Out-File -Append $logFile
    "Server: $server_name" | Out-File -Append $logFile
    "Database: $database_name" | Out-File -Append $logFile
    "Exact Match: $exact_match" | Out-File -Append $logFile
    ""  | Out-File -Append $logFile


    if ($server_name -eq "" -or $server_name -eq $null)
    {
        $server_name = $env:computername
    }

    $sql_services = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "$server_name" -ErrorAction Stop

    foreach ($sql_service in $sql_services) 
    {
        $instance_name = $sql_service.Name -replace "MSSQL\$", ""
        if ($sql_service.State -eq "Running")
        {
            $sql_connection = if ($instance_name -eq "MSSQLSERVER") { $sql_service.PSComputerName } else { $sql_service.PSComputerName + "\" + $instance_name }

            #$sql_connection = $sql_service.PSComputerName + ($instance_name -eq "MSSQLSERVER" ? "" : "\" + $instance_name)

            if ($database_name -eq "")
            {
                Get-SqlDatabase -ServerInstance $sql_connection 
            }
            else
            {

                if ($exact_match -eq "Y")
                {
                    Get-SqlDatabase -ServerInstance $sql_connection | Where-Object {$_.name -eq $database_name} | ft -AutoSize
                }
                else
                {
                    Get-SqlDatabase -ServerInstance $sql_connection | Where-Object {$_.name -like "*$database_name*"} | ft -AutoSize
                }
            }


        }
        else
        {
            "Skipping $sql_connection as it's not running..." | Out-File -Append $logFile
        }
    }

    ""  | Out-File -Append $logFile
    "Completion Time: " + (Get-Date)  | Out-File -Append $logFile
  
}
Catch
{
    $errorMessage = (Get-Date).ToString() + ": Error Occurred - " + $_.Exception.Message
    $errorMessage | Out-File -Append $logFile
    throw
}

# Launch the notepad.exe to view the log file
# notepad $logfile

Download this PowerShell script from GitHub at:

Find SQL Databases

 
I have conducted some initial testing on this script, but it has not undergone extensive or thorough testing. Additionally, I have not tested it on SQL servers hosted on Linux platforms. Therefore, I would greatly appreciate your feedback. If you decide to test this script, please feel free to share your thoughts, experiences, and suggestions for enhancements, or point out any errors or issues you encounter.