4

This question already has an answer here:

I have 100+ sql server instances (from sql 2005 to sql 2016) installed in my environment, is there a good way to find when an instance was initially installed?

I tried to check the [master] database creation date, and found (for sql 2012 onward at least) it is always Apr 8, 2003.

The only possible date is [msdb] creation date, which I see it changing for different sql server instances. (I am not sure about this is because I am not sure whether a service patch will change the [msdb] creation date?)

Is there any other reliable way that can indicate the sql server instance creation date? I am open to use of t-sql or powershell to figure it out.

Thanks in advance.

marked as duplicate by Tom V, McNets, Colin 't Hart, James Anderson, Marian May 25 '17 at 12:12

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3

When installing a sql server instance, NT AUTHORITY\SYSTEM login is created , so you can get the instance installation date by searching for the NT AUTHORITY\SYSTEM login created date.

We can query one of the views to get the installation date. If your SQL Server is English Language compatible you can directly query by login name or for the other languages we will use the neutral language (hexadecimal code) which is same on every instance.

Note: the NT AUTHORITY\SYSTEM login SID is 0x010100000000000512000000

-- work with only English language installations
SELECT  createdate as 'SQL Server Installation Date'
FROM    sys.syslogins 
where   name = 'NT AUTHORITY\SYSTEM'

--neutral language 
SELECT  createdate as 'SQL Server Installation Date'
FROM    sys.syslogins 
where   sid = 0x010100000000000512000000

--Using sys.server_principals 
SELECT create_date as 'SQL Server Installation Date'
FROM sys.server_principals 
WHERE name='NT AUTHORITY\SYSTEM'

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

There are a lot of articles providing similar solutions:

  • @jyao if this answer is what u are looking u have to accept it. Happy to help :) – Hadi May 25 '17 at 17:21
5

This query:

SELECT create_date
FROM sys.server_principals
WHERE sid = 0x010100000000000512000000
  • 1
    Really? Is that value for SID going to be the same across all instances?? – Colin 't Hart May 25 '17 at 7:30
  • 2
    Yep, maybe not so elegant, but it is widely used. – user_0 May 25 '17 at 7:32
  • This sid is related to NT AUTHORITY\SYSTEM login. Check my answer for more details. – Hadi May 25 '17 at 16:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.