How can I check the version and edition of SQL Server installed?

orange

********* UPDATE 05/08/2019 *********

It’s been a while (4 years!) since using this script, and have found a few flaws in its logic and execution, especially when it comes to SQL 2000 instances (yes, they’re still very much out there).

The script below has now been updated and tested and should work correctly on servers that have multiple SQL instances installed.

****************************************

 

Original Post May 1st 2015

When you need to perform an audit of SQL Server installations (by edition) across a huge number of servers, this script should save you a whole lot of time.

Assuming your servers are online and not being blocked by the Windows Firewall, run the below script, and your output should appear in the CSV specified at the start of the script in the $outfile variable.

You might want to drop the latter output commands for hosts that are not online or do not have any SQL registry keys found, to reduce the noise in your output file, but I’ll leave them included here.

 

All things being equal, you should get something like this (which you can bring into Excel if you need to):

 

And here’s a handy chart for converting those version numbers into something more meaningful: http://sqlserverbuilds.blogspot.com.au/

orange

10 Comments How can I check the version and edition of SQL Server installed?

  1. vegas4

    Make sure there is no space after the computer name in “servers.txt”, that will result in a failure to test the connection to the host (ask me how I know…)

    Reply
    1. Kamal

      Painful, but a good lesson to learn, I suppose. I might start using the Trim method when getting server names from untrusted sources (ie; not straight straight from Active Directory) just in case there are any pesky white spaces before or after.

      Reply
    1. Kamal

      If you check the link I provided at the end of the article, it shows you which version numbers are for each Service Pack. You just need to re-write the switch section of the script to include the extra numbers to delineate the Service Pack information.

      Reply
  2. Sam Lee

    Thanks..very good script and works great. Is there any script like this to find out all the databases and their sizes as well using list of servers

    Reply
    1. Kamal

      You have two choices (since you can’t pass credentials in the script as it’s written):

        use “run-as” for your PowerShell window with an account that has the necessary permissions on the remote computer (this is the easiest method and how I’d do it)

      OR

        switch to using WMI to query the registry instead of: Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $server) (this would require a lot of changes to the script)
      Reply
  3. Lisa Lyons

    Is there a way to modify it so that if you have multiple instances on the same machine it reads them all?

    Reply

Leave a Reply

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