Detect if an instance of SQL Server has been upgraded

Question

Detect if an instance of SQL Server has been upgraded

kiran.hegde

SSC-Addicted

Points: 414
More actions
April 11, 2010 at 4:19 am

#233668

I would like to know a reliable way of detecting if a particular instance of SQL Server has been upgraded from an earlier version/if the instance has been installed fresh
i.e i would like to know if there is a reliable way to figure out if a particular SQL 2008 instance has been ugpraged from a lower version such as SQL 2K/SQL 2k5 or if the SQL 2008 instance is a fresh install ?
Does anyone know if there is a reliable way to do this?
Regards,
Kiran Hegde

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

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

The column file_guid in sys.master_files will be NULL if the database was upgraded from a previous version. Is that what you are after?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kiran.hegde SSC-Addicted Points: 414 More actions · Answer 2

Probably you have answered my question.

Let me verify it and let you know.

How reliable is this approach?

Is this going to be the way you have described for all upgrade paths listed below:

SQL 2K --> SQL 2k8

SQL 2k5 --> SQL 2k8

SQL 2K + SP --> SQL 2K8

SQL 2k5 + SP --> SQL 2K8

Are you aware of any other approach apart from this?

Thanks in advance

Paul White SSC Guru Points: 150467 More actions · Answer 3

kiran.hegde (4/11/2010)
Probably you have answered my question.
Let me verify it and let you know.

Ok 🙂

How reliable is this approach?

Well it is documented by Microsoft in the link I gave you, so I'd say it's pretty solid 😉

Is this going to be the way you have described for all upgrade paths listed below:

I'd have to double-check the 2005 version of the link I sent you.

Are you aware of any other approach apart from this?

That was the first documented one that came to mind.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kiran.hegde SSC-Addicted Points: 414 More actions · Answer 4

This does not seem to be as documented.

The column file_guid contains a combination of both NULL and non NULL values.

It seems to be identical for both fresh install and an upgrade.

Any more ideas?

Paul White SSC Guru Points: 150467 More actions · Answer 5

kiran.hegde (4/12/2010)
This does not seem to be as documented.
The column file_guid contains a combination of both NULL and non NULL values.

It is per database - I think I mentioned that...?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kiran.hegde SSC-Addicted Points: 414 More actions · Answer 6

i do remember that.

However when a SQL Server instance is upgraded, the value should be NULL for all databases on the sql instance. That is not the case.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 7

Other than curiosity why would you want to know this, just curious myself?

Andrew

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

kiran.hegde (4/12/2010)
However when a SQL Server instance is upgraded, the value should be NULL for all databases on the sql instance.

Who said that?

Documentation Paul provided clearly reads "master" database 😉

Don't you want to check the status of the instance? What's a better place than master?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Paul White SSC Guru Points: 150467 More actions · Answer 9

PaulB-TheOneAndOnly (4/14/2010)
kiran.hegde (4/12/2010)
However when a SQL Server instance is upgraded, the value should be NULL for all databases on the sql instance.
Who said that?
Documentation Paul provided clearly reads "master" database 😉
Don't you want to check the status of the instance? What's a better place than master?

😉 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kiran.hegde SSC-Addicted Points: 414 More actions · Answer 10

I find that file_guild value is NULL for the master database irrespective of whether it is an upgraded SQL instance or a fresh install of a SQL Server instance.

Help please.

kiran.hegde SSC-Addicted Points: 414 More actions · Answer 11

It looks like finally there is an answer which is very close to what i am looking for

Take a look at:http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/c99a2163-025e-424b-82cb-78ae5dd89f44

This is the MSDN posting where i was following up.

Paul White SSC Guru Points: 150467 More actions · Answer 12

kiran.hegde (4/21/2010)
It looks like finally there is an answer which is very close to what i am looking for Take a look at:http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/c99a2163-025e-424b-82cb-78ae5dd89f44 This is the MSDN posting where i was following up.

Cool. In case you were wondering, page 9 of a database's first primary data file is the 'database boot page'.

See http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-20-Boot-pages-and-boot-page-corruption.aspx and http://technet.microsoft.com/en-us/magazine/2008.08.sqlqa.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kiran.hegde SSC-Addicted Points: 414 More actions · Answer 13

kiran.hegde

SSC-Addicted

Points: 414

April 22, 2010 at 3:26 am

#1155934

Thanks for the help.