Script to Find SQL Server Installation Date and Time

How to get SQL Server Installation Date and Time ?

I have been asked this question by one of my colleague says ‘ How retrieve SQL Server Installation Date and Time ‘ . I immediately told him to do some research on this. Yes, he did and  came back to me with a script which gives you the required output Installation Date and Time of SQL Server. 

This is a Awesome script to find SQL Server Installation Date and Time. I asked him how you have did this, he said this script was found in one of the blog when doing google search. Search Online and get your solutions . I also googled to see who has developed this script but i saw same script in most of the blog and sites . Also not sure who is the original  creator of this script . We have to thank the person who has written this script.

For getting the exact SQL Server installation date we have to check for the object which is created at the time of installation. NT Authority\System login is getting created at the time of SQL Server installation. You can check the SQL Server installation date by querying the sys.syslogins or sys.server_principals view against the login NT Authority\System name.

NT Authority\System login which has unrestricted access to all local system resources and it is a member of the Windows Administrators group on the local computer with the sysadmin fixed SQL Server role.NT Authority\System login get created at the time of installation of SQL Server.

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.

— 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’

Where SID 0X010100000000000512000000 is belongs to user “NT AUTHORITYSYSTEM“. This user create at the time of installation only.

If you have evaluation edition installed in your machine and you want to find the expire date when it is going to expire.

All knows SQL Server Evaluation Version Period is 180 days . If you are looking for a script to get the Expiry Date of SQL Server Evaluation Version

Below query is the query to find the answer and enter the product key to activate the SQL Server license.

— Evaluation version expire date
SELECT create_date as ‘SQL Server Installation Date’,
DATEADD(dd,180,create_date) as ‘Expiration Date’
FROM sys.server_principals WHERE name=’NT AUTHORITY\SYSTEM’

Hope this helps you a lot .

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.