When you have a whole bunch of SQL Servers with multiple instances and don’t know which version on each instance you’re running on what server, you may want to do a little inventory.
But would you (or tell an intern to) logon to those servers and do it manually? I sure wouldn’t… PowerShell can help you with this 🙂
So, how can you find the version of SQL? I’ve chosen to look at the file ‘sqlservr.exe’ and get the version of that file.
This because that would give me a pretty darn accurate answer to that question 🙂
So, did you know that you can use the CIM_DATAFILE class to find files on a server?
Get-CimInstance -Query "SELECT * FROM CIM_DataFile WHERE Drive='C:' AND FileName='sqlservr' AND Extension='exe'"
This will give you a version of the file and based on the location of the file you can deduct which instance it belongs to.
Note that I’ve defined the drive where to search. If you don’t, it will search on all drives.
Depending on your target, it may take quite some time….