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.
Hey Jess, nice post. I have used Find-DbaInstance to get the instance name too, i.e.
Find-DbaInstance -ComputerName ThisIsMadness | SELECT SqlInstance
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!