The Multifunctioning DBA

Jan 8 2010   7:19PM GMT

Find SQL Server Instances using Powershell



Posted by: Colin Smith
Tags:
MS SQL Server
MSSQL Administration
MSSQL Instance
Powershell
Scripting

As I had mentioned before, I recieved a network scan that lists the host names of servers that have some sort of SQL Server running on them. Now I need to get a list of all instances that are on those servers. I thought I could do it in Powershell and I was correct. I know that all instnances of SQL Server have to have a SQLSERVER service associated with it. Knowing this I thought I could use the get-service cmdlet in order to list all the services on each host. This was a great idea but can not work. I am using Powershell V1 and the get-service cmdlet will only work on the local host. I was not going to install Powershell on each host in order to find the instance names, that would be a ton more work and I am into doing less work not more. I think most IT people in general are that way.

After thinking about another way I could get what I needed, a light turned on and shined down on my from the Powershell gods, Jeffery Snover perhaps, and I knew what to do. WMI is the answer, why did I not think of that sooner? Anyway, I can do a get-wmiobject -computername “name” win32_service and that will return a list of services on that machine. Now all I have to do is filter the results to get only that which I desire.

Just want to mention that the script does have one flaw that I did not fix. If you do not have access to connect to WMI on the server the script will stop. If the server is unreachable for some other reason, RPC Unavailable, that server will be put into a list of servers that were not available and the script will continue.

I think that this script could be useful for others in a similiar postion to me. Walking into an environment where the previous SQL Admin did not really leave any documentation for you so you are walking in blind with no guide at all. Please let me know if you have any questions.



############################################################################## ## Author: Colin Smith ## ## Script: Get_Intstance_names.ps1 ## ## Purpose: This script will read in a file of hotnames that has been ## ## Provided of servers with SQL Server running on them. This ## ## script will then look at the services on that host to find ## ## the instance name if the instance is named. If the instance ## ## is a default instance the script will also report that. ## ############################################################################## $servers = get-content "C:\servers.txt" echo "Server, Instance" >> "C:\sqltab.txt" foreach ($server in $servers) { $instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"} if (!$?) { echo "Failure to connect on $server" >> "C:\failures.txt" echo "Failure to connect on $server" } Else { $instancenames = @() foreach ($name in $instances) { if (($name.name -eq "MSSQLSERVER") -or ($name.name -like "MSSQL$*")) { $instancenames += $name.name } } foreach ($iname in $instancenames) { echo "$server, $iname" >> "C:\sqltab.txt" echo "$server, $iname" } } }

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: