Thursday, April 25, 2019

How to find out when SQL Server was installed?

Good question.  How do you find out when SQL Server was installed?  I believe there may be a few different methods but the one that I use is pretty simple.  I just query the system for something that is created at the time of the install --- the NT AUTHORITY\SYSTEM login.  Pretty easy... as you can see here.


       -- SERVERPROPERTY
       SELECT
              SERVERPROPERTY('productversion') ProductVersion,
              SERVERPROPERTY ('productlevel') ProductLevel,
              SERVERPROPERTY ('edition') Edition,
              SERVERPROPERTY ('MachineName') MachineName,
              create_date 'SQL Server Installation Date'
       FROM
              sys.server_principals
       WHERE
              name='NT AUTHORITY\SYSTEM'


Here's the output from one of my instances:


And that's that!



4 comments:

  1. As the LSA-Prefix (NT AUTHORITY) is localized depending on the OS-Language, would it make sense to filter for `name LIKE '%\SYSTEM'` so that e.g. the German version (NT AUTHORITÄT\SYSTEM) is found? Or does SQL Server translate the account names to an english form of the sAMAccount name?

    Best
    Daniel

    ReplyDelete
    Replies
    1. That is a very good point, Daniel. We're not all speaking English... this tested out fine on my instance:

      WHERE name LIKE '%\SYSTEM'

      Delete
  2. Interestingly enough on my instances where I've performed in-place upgrades, the new ProductVersion shows in column 2, but the original installation date in the last column.

    ReplyDelete
    Replies
    1. Yes, very interesting, Ooogy, but I believe that corresponds to when sql was installed. If you're looking for the date of the upgrade, you may want to use modify_date.

      installed = create_date, upgrade = modify_date

      You could add this into your statement and confirm whether it reflects your upgrade date:

      modify_date 'SQL Server Upgrade Date'

      Delete