Close

Get a list of databases from named SQL Instances

Have you ever had someone send you the name of a SQL Server and database to do some work, but when you try to connect to the server you can’t? Then,come to find out, there are four named instances on the box and you don’t know which one hosts the database? No? Just me?

Luckily, dbatools has a couple of commands that can help us out with this. Firstly, we can use Get-DbaService to get a list of instances that are running on the server:

$SqlInstances = Get-DbaService -ComputerName mssql1 -Type Engine |
Select @{L='SqlInstance';e={('{0}\{1}' -f $_.ComputerName, $_.InstanceName)}}

I went ahead and piped this to the Select-Object and built the SqlInstance property to be ‘ServerName\InstanceName’.  We can now use this in any of the other dbatools commands. For my use case I wanted database information, so I went with Get-DbaDatabase:

Get-DbaDatabase -SqlInstance $SqlInstances.SqlInstance |
Format-Table SqlInstance, Name, Status, RecoveryModel -AutoSize

This made it easy for me to find the database in question without having to connect to each instance manually.

You could also use this if you had a list of servers by just passing in a comma seperated list to the -ComputerName parameter on Get-DbaService.

Just a short post today, but hopefully useful to somebody.

2 thoughts on “Get a list of databases from named SQL Instances

    1. Thanks Mikey! This command was also pointed out on Twitter as well, I had always been apprehensive about using it as it mentioned scanning the network. While I was doing more research on it today it looks like that’s only if you use certain parameters, Chrissy has written about it here: https://dbatools.io/find-sql-instances/.

      Good find!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: