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

orange

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.

To start with, get a list of servers into a .txt file – one server name per line.  In my example, here’s a sample of what c:\temp\servers.txt looks like:

sqledition1

 

With that sorted, assuming your servers are online and not being blocked by the Windows Firewall, run the below script, and your output should appear as c:\temp\sqlversioncheck.csv

 

 

All things being equal, you should get something like this:

sqledition2

 

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

orange

8 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

Leave a Reply

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