Determining the Actual Server Create Date

I am interested in determining the ages of all 70+ my servers. I would like to create a growth graph showing when servers and databases were added to the environment either/both monthly or annually. I executed the following query using the SSMS multi-query feature with only partial success.

SELECT [name], create_date FROM sys.databases

It's partial success because the master and model databases return with a date of 'April 8, 2003' when I know that I created some of these servers during the past couple years.

I refresh databases from one server to another several times a week, which appears to be causing issues with the database create date.

Is there a simple method to determine the actual create date for both servers and databases?

determine the actual create date for SQL Server

This can be found using

SELECT  createdate as SQL_SERVER_INSTALLATION_DATE
FROM    sys.syslogins 
where   sid = 0x010100000000000512000000

Note : sys.syslogins is deprecated, so using sys.server_principals will give you the same results.

So you can use :

--Using  sys.server_principals
SELECT SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + ' 
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation
      ,create_date as 'SQL Server Installation Date'
FROM sys.server_principals 
WHERE name='NT AUTHORITY\SYSTEM'

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

Actual creation of database

This can be found by reading the boot page of the database.

Note: DBCC PAGE is undocumented. I have not seen any side effects of using it, but since it is undocumented, use it with caution.

DBCC TRACEON(3604)
GO
 -- Actual creation time of database is stored in the boot page of the database
 -- This is even retained after restore or detach/attach of the database. 
 --- Look for dbi_crdate.
DBCC PAGE('database_name_Goes_here', 1, 9, 3)
GO

DBCC TRACEOFF(3604)
GO

Even better using DBCC PAGE WITH TABLERESULTS

CREATE TABLE PageData
(
      ParentObject VARCHAR(1000)NULL,
      Object VARCHAR(4000)NULL,
      Field VARCHAR(1000)NULL,
      ObjectValue VARCHAR(MAX)NULL,
)
GO   

INSERT INTO PageData (ParentObject, Object, Field, ObjectValue)
EXEC ('DBCC PAGE (''AdventureWorks2008R2_copy_August_2014'',1, 9, 3) WITH TABLERESULTS')
GO

select DatabaseName = (select ObjectValue from PageData where field ='dbi_dbname')
, DatabaseCreateDate = ( select ObjectValue from PageData where field = 'dbi_crdate')

StackExchange Database Administrators Q#73817, answer score: 5