Tag: SQL Server Installation Date

  • 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'

  • How to Check When SQL Server was installed?

    NT AUTHORITY\SYSTEM login gets created when we install SQL Server. By querying sys.syslogins, we would get an idea of the SQL Server Installation date time as per the creation date time of NT AUTHORITY\SYSTEM.

    SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0x010100000000000512000000

    SELECT * FROM sys.syslogins WHERE sid = 0x010100000000000512000000

    SELECT createdate AS sql_server_install_date FROM   sys.syslogins WHERE  sid = 0x010100000000000512000000

SELECT * FROM   sys.syslogins WHERE  sid = 0x010100000000000512000000