Instance installation date

Question

Instance installation date

river1

SSC-Insane

Points: 24309
More actions
May 7, 2014 at 9:18 am

#307144

Hi,
I would like to know in with date did a SQL Server 2005 instance was installed on a server.
Can I know This using t-sql?
Other question that I have is if I can know if there was a SQL Server installed on the same server and in wich date it was unistalled.
Thank you

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

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

river1 (5/7/2014)
Hi,
I would like to know in with date did a SQL Server 2005 instance was installed on a server.
Can I know This using t-sql?

from glenn Berrys diagnostic queries

-- When was SQL Server installed

SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date]

FROM sys.server_principals WITH (NOLOCK)

WHERE name = N'NT AUTHORITY\SYSTEM'

OR name = N'NT AUTHORITY\NETWORK SERVICE' OPTION (RECOMPILE);

---------------------------------------------------------------------

river1 SSC-Insane Points: 24309 More actions · Answer 2

Thank you.

What about the restores that were made.

Can I having only a database know how many restores were made to it?

I cannot use the msdb because I want to see what restires were made before the SQL Server instance was installed.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

without the msdb you cannot retrieve that information.

If you still have the old msdb as a backup or OS files you could restore or attach it as a user database under a different name and then extract the information out of restorehistory in the normal way.

---------------------------------------------------------------------

river1 SSC-Insane Points: 24309 More actions · Answer 4

Maybe they have the old OS files. I will check with them.

Thanks

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

george sibbald (5/7/2014)
river1 (5/7/2014)
Hi,
I would like to know in with date did a SQL Server 2005 instance was installed on a server.
Can I know This using t-sql?
from glenn Berrys diagnostic queries
-- When was SQL Server installed
SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date]
FROM sys.server_principals WITH (NOLOCK)
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE' OPTION (RECOMPILE);

Be very careful with NOLOCK and READ UNCOMMITED, apart from possibly bringing back incorrect data these two options will not be supported in future versions of sql server.

😎

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Eirikur Eiriksson (5/7/2014)
george sibbald (5/7/2014)
river1 (5/7/2014)
Hi,
I would like to know in with date did a SQL Server 2005 instance was installed on a server.
Can I know This using t-sql?
from glenn Berrys diagnostic queries
-- When was SQL Server installed
SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date]
FROM sys.server_principals WITH (NOLOCK)
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE' OPTION (RECOMPILE);
Be very careful with NOLOCK and READ UNCOMMITED, apart from possibly bringing back incorrect data these two options will not be supported in future versions of sql server.
😎

I'll tell Glenn 🙂

Wasn't expecting the server name or create date to change.

---------------------------------------------------------------------

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Eirikur Eiriksson (5/7/2014)
george sibbald (5/7/2014)
river1 (5/7/2014)
Hi,
I would like to know in with date did a SQL Server 2005 instance was installed on a server.
Can I know This using t-sql?
from glenn Berrys diagnostic queries
-- When was SQL Server installed
SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date]
FROM sys.server_principals WITH (NOLOCK)
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE' OPTION (RECOMPILE);
Be very careful with NOLOCK and READ UNCOMMITED, apart from possibly bringing back incorrect data these two options will not be supported in future versions of sql server.
😎

NOLOCK is deprecated but READ UNCOMMITED is not.

http://technet.microsoft.com/en-us/library/ms187373.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

Eirikur Eiriksson

SSC Guru

Points: 182912

May 7, 2014 at 10:28 am

#1711612

My memory playing tricks on me:-P

Thanks Sean!

😎

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Eirikur Eiriksson (5/7/2014)
My memory playing tricks on me:-P
Thanks Sean!
😎

I lost my memory long ago...thank {insert your supreme being here} for BOL. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 10

Sean Lange (5/7/2014)
Eirikur Eiriksson (5/7/2014)
My memory playing tricks on me:-P
Thanks Sean!
😎
I lost my memory long ago...thank {insert your supreme being here} for BOL. 😀

BOL even slower than my memory when I'm using the mobile 😛

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 11

Sean Lange (5/7/2014)
Eirikur Eiriksson (5/7/2014)
george sibbald (5/7/2014)
river1 (5/7/2014)
Hi,
I would like to know in with date did a SQL Server 2005 instance was installed on a server.
Can I know This using t-sql?
from glenn Berrys diagnostic queries
-- When was SQL Server installed
SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date]
FROM sys.server_principals WITH (NOLOCK)
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE' OPTION (RECOMPILE);
Be very careful with NOLOCK and READ UNCOMMITED, apart from possibly bringing back incorrect data these two options will not be supported in future versions of sql server.
😎
NOLOCK is deprecated but READ UNCOMMITED is not.
http://technet.microsoft.com/en-us/library/ms187373.aspx

Found it again here Deprecated Database Engine Features in SQL Server 2014, my memory is worse than I remembered:w00t:

😎

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Eirikur Eiriksson (5/7/2014)
Sean Lange (5/7/2014)
Eirikur Eiriksson (5/7/2014)
george sibbald (5/7/2014)
river1 (5/7/2014)
Hi,
I would like to know in with date did a SQL Server 2005 instance was installed on a server.
Can I know This using t-sql?
from glenn Berrys diagnostic queries
-- When was SQL Server installed
SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date]
FROM sys.server_principals WITH (NOLOCK)
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE' OPTION (RECOMPILE);
Be very careful with NOLOCK and READ UNCOMMITED, apart from possibly bringing back incorrect data these two options will not be supported in future versions of sql server.
😎
NOLOCK is deprecated but READ UNCOMMITED is not.
http://technet.microsoft.com/en-us/library/ms187373.aspx
Found it again here Deprecated Database Engine Features in SQL Server 2014, my memory is worse than I remembered:w00t:
😎

Excellent. Thanks for the correction to my correction. 😉 In fact, in the article I posted it clearly states that both are deprecated. I am glad to see them go away. I never use either of them and they tend to cause lots of issues for people who don't fully understand what they do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 13

Sean Lange (5/7/2014)
I never use either of them and they tend to cause lots of issues for people who don't fully understand what they do.

NOLOCK turns the database into a box of chocolates, you never know what you are going to get.....:crazy:

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Eirikur Eiriksson (5/7/2014)
Sean Lange (5/7/2014)
I never use either of them and they tend to cause lots of issues for people who don't fully understand what they do.
NOLOCK turns the database into a box of chocolates, you never know what you are going to get.....:crazy:

LOL. That is awesome!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/