SQL Server Instance Installation Date

Background

Clearing up my heavy stack of dried woods masquerading has white papers.

I really like a post courtesy of Tim Radney.

In the specific post he spoke to how to get SQL Server Installation Date based on looking at sys.server_principals and fetching the record for NT AUTHORITY\SYSTEM.

He says the create_date column for that record will bear the SQL Instance Install date.

 

Code

Tim Radney’s Version


SELECT create_date
FROM sys.server_principals
WHERE sid = 0x010100000000000512000000

 

Customized Version



;with ctePrincipal
(
	  [principal]
	, [datePrincipalCreated]
)
as
(

	select top 1
			  tblSSP.[name]
			, tblSSP.[create_date]

	FROM sys.server_principals tblSSP 

	where
	(
			( tblSSP.[name] like 'NT AUTHORITY%' )
		or  ( tblSSP.[name] like 'NT SERVICE%' )
	)

	order by
		tblSSP.[create_date] asc
		

)
SELECT 
		  tblSSP.name

		, tblSSP.[type_desc]

		, [dateCreated]
			= tblSSP.create_date
		
FROM sys.server_principals tblSSP 

INNER JOIN ctePrincipal cteP
	on tblSSP.[name] = cteP.[principal]




Output

 

Explanation

  1. I changed Tim Radney’s code a bit
    • I did not want to hardcode NT AUTHORITY\SYSTEM
    • And, instead looked at all the records in the targeted table ( sys.server_principals )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s