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!
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?
ReplyDeleteBest
Daniel
That is a very good point, Daniel. We're not all speaking English... this tested out fine on my instance:
DeleteWHERE name LIKE '%\SYSTEM'
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.
ReplyDeleteYes, 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.
Deleteinstalled = 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'