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


SELECT 
		  tblSSP.name

		, tblSSP.[type_desc]

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

inner join sys.certificates tblSC

	on tblSSP.[name] = tblSC.[name]

	 
order by
		tblSSP.create_date desc



Output

installationdatebasedoncertificates

 

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 )
    • It appears that there is a pattern around Certificate Mapped logins and that pattern will serve same purpose

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