How to Find When SQL Server was Installed?
Have you ever thought when SQL Server instance was installed on your machine? If you don’t have idea about finding SQL Server installation date then you should keep reading this article where i have described how to know when SQL Server was installed on your database server?
There are many ways to check SQL Server installation date on your machine. Here i am going to discuss two popular methods that we can use easily to get install date of SQL Server. Let’s go one by one in step by step process.
As we know whenever we install SQL Server, a SQL Server Agent job named “syspolicy_purge_history” got created. The install date of SQL Server instance will be same as the create date of this job because this job was created during SQL Server installation. So, if you need to find when SQL Server was installed on your database server then you can get it by create date info of above job. Follow below steps to get the details.
You can expand SQL Server Agent folder in SSMS and then Jobs folder. Here you can see this job. We can launch Properties window of job “syspolicy_purge_history” or you can query msdb database to get the details of this job. Here, I have launched Properties windows of this job and we can see the create date of this job is below screenshot. It means this SQL Server Instance was installed on 27th July 2017 as showing as create date of below job.
Another method to get SQL Server install date on your database server is by using SQL Server system logins. These system logins get created when we install SQL Server. Some of the system logins are NT SERVICE\SQLWriter, NT Service\MSSQLSERVER, NT AUTHORITY\SYSTEM and NT AUTHORITY\NETWORK SERVICE. You can also see some of the system logins that starts with hash # like ##MS_Policy***** etc. As I said these logins get created during SQL Server installation so to get SQL Server installation date we can check the create date of any such logins.
You can run below commands to check the create date of any of system logins. I have executed below commands to check create date of three system logins.
SELECT name, create_date FROM sys.server_principals WHERE name='NT SERVICE\SQLWriter' GO SELECT name, create_date FROM sys.server_principals WHERE name='NT Service\MSSQLSERVER' GO SELECT name, create_date FROM sys.server_principals WHERE name='NT AUTHORITY\SYSTEM' GO
We can see create date of all above SQL Server system logins are same as the create date of system job “syspolicy_purge_history” that is described in method 1. So, SQL Server install date on this database server was 27th July 2017 as shown in below image.
You can also get SQL Server install date by getting update date of system account sa. As we have disabled sa account as part of SQL Server hardening so last update was captured when we disabled this account. But this does not show correct info always because if anybody has enabled this account and again disabled it then this will give you inaccurate information but you can use this information to compare the update date information with the create date of other logins and jobs. This will help you in identifying the install date of SQL Server in more accurate manner.
SELECT * FROM syslogins WHERE name='sa' GO SELECT * FROM sys.server_principals WHERE name='NT AUTHORITY\SYSTEM' GO
You can see the update date of sa account and create date of the another system login shows same date that is also the SQL Server Installation date.
- SQL Server Evaluation Period has Expired and How to Upgrade it?
- Create a Logon Trigger to Restrict sysadmin logins to Connect to SQL Server during a given time Interval
- SQL Server Interview Questions & Answers
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018