How to find out the SQL Server installation date?


Problem
How to find out the SQL Server installation date?

Solution:
To get 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.

First we will check the sys.syslogins or sys.server_principals views
sys.syslogins
This SQL Server 2000 system table is included as a view for backward compatibility which shows all logins, its metadata and access.

sys.server_principals
Contains a row for every server-level principal

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'

Query to check the SQL Evaluation Version Expire Date
You can check the SQL Server evaluation version expire date as well using below query 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'
Advertisements

2 thoughts on “How to find out the SQL Server installation date?

  1. desperadomar

    How about using the sql server installation log file in %programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\
    will give a plethora of information about the installation.I used to find the installation date by this way anyways thanks for this new tip by querying sql server,it’s easy way to query it on remote servers rather logging into the server

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

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