Can I determine SQL install date using a TSQL script?

Question

Post reply

Can I determine SQL install date using a TSQL script?

Graham.Okely-876987

Say Hey Kid

Points: 694
More actions
November 25, 2008 at 10:36 pm

#126076

Is it possible to determine the date SQL was installed using a TSQL script command?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Graham.Okely-876987 Say Hey Kid Points: 694 More actions · Answer 1

I would like to know when several SQL Servers were created.

Could it be the date the earliest non system database was created?

select @@servername + ' '+ convert(varchar(20),crdate,111) + ' '+ [Name] from sys.sysdatabases order by crdate desc

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Just curious... why do you need to do this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Graham.Okely-876987 Say Hey Kid Points: 694 More actions · Answer 3

Mainly because I am decommissioning SQL servers and building some SQL servers concurrently. I want to let people requesting them know that the life of the servers is too short, if in fact, it is. As I am contracting I do not know, for certain, when they were made.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

The script above should help, but why would that date impact whether or not something was being decommissioned? I'd think it was being used or not.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Try looking at the installation folder's creation dates

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Graham.Okely-876987 Say Hey Kid Points: 694 More actions · Answer 6

I think that the ability create virtual servers can mean less thought is going into their design. Therefore they need to be removed. I want to know how long they last. I think we need to plan them better so they last longer.

By the way finding out the installation date via T-SQL means I can run the command across multiple servers. Then there is no need to go to windows explorer for folder date in each instance.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

rbarryyoung (11/30/2008)
Try looking at the installation folder's creation dates

This can be done from SQL server.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Graham.Okely-876987 Say Hey Kid Points: 694 More actions · Answer 8

Is that using DIR ? How would you get that to work over different install paths?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Ummmm... you may want to double check the methods above if these are, in fact, virtual machines... even the installation folders may contain nothing but an image of what the "mother" VM machine has on it... could be real deceiving. Not sure where to look on VM machines, but can find out at work, today.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Thats a good point, Jeff. I had not been thinking of VM differences.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Hmm, not sure abut Install paths, but here is what I use to find the Data directory for Master.mdf. I have added the folders Date Created also:

Create proc spFindMasterdataDirectory as

--====== Now, Find the load folder and Load the Assembly:

Declare @File nvarchar(128), @cmd nvarchar(128)

Select @File = Left(physical_name, Len(physical_name)-Len('\Data\master.mdf'))

from master.sys.database_files

where name = 'master'

Select @cmd = 'DIR "'+@File+'" /TC /AD '

Create Table #tmp(id int identity, txt NVarchar(128))

INSERT Into #tmp(txt)

Exec xp_CmdShell @cmd

Select Cast(Left(txt,20) as DateTime) as [CreatedDate]

, Substring(txt,40,Len(txt)) as [DirName]

From #tmp

Where ID > 4 And Left(txt,1) IN ('0','1')

And Substring(txt,40,Len(txt)) = 'Data'

Drop Table #tmp

go

It does make some assumption though, so you might want to check them.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 12

VMs have a config file and virtual disk file, which have creation dates, but again, I'm not sure, whether thought out or not, that this has something to do with removal. A candidate would be not used, so perhaps last modified date on the MDF makes more sense?

mcjohn87 Valued Member Points: 71 More actions · Answer 13

thanks so much for useful info

http://globolstaff.com

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 14

How about one more solution:

use master

go

select createdate as 'Sql Server Installation Date'

from sys.syslogins

where sid = 0x010100000000000512000000 -- language neutral for NT AUTHORITY\SYSTEM

go

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."