April 26, 2012 at 1:45 pm
I was posed with a question today that I could not completely answer.
Supposed a database exists on a server, for arguments sake say SQL 2008. From the database alone, can you tell if it was upgraded form a 2000 or 2005 database?
I figured you could troll thru the sql logs and look for a restore, but if those logs have rolled over long ago, is there anything in the metadata that would point to it having once been a previous version?
Lowell
April 26, 2012 at 1:52 pm
Not directly, so far as I know. You might be able to gleen some clues from the create date in sys.databases. If it was created before 2008, odds are it was a prior version of SQL Server. But that's not a solid test, since it could easily have been created in a prior version after 2008, or might possibly have been created in a beta copy of 2008 before 2008. Also, the create date will be "off" (not the original) if it was created as a copy from a backup, for example. But it might give you a bit of data about it.
Compatibility level might also be a clue. It would be unusual to create a new SQL 2008 database in compat 80 or 90. Not impossible, but not probable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 26, 2012 at 1:58 pm
about the only thing i can find so far that implies a previous version in my sample system is in sys.databases like you pointed out, the previously "default" setting of page_verify_option_desc= TORN_PAGE_DETECTION,
where the value for 2008 and above is CHECKSUM, but that is for the handlful of databases that i know came from 2005, since i did those two myself.
my restored-from-2005 have create dates that are whent hey were created on the server, and not when originally created, so that's out except for inplace upgrades, i think.
since that database setting, just like the compatibility level, can be changed, it's not 100%, but it's a good indicator;
i just thought some column, say in sys.objects was say, always null from previous versions, as it didn't exist, but it does for all items after...that kind oif thing.
Lowell
April 26, 2012 at 2:00 pm
Nothing I know of does that.
That's what database inventories and documentation are for, really. I have an internal wiki where that kind of thing gets documented.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 26, 2012 at 2:09 pm
Yup, absolutely there is. It's one of those undocumented features.
DBCC TRACEON (3604)
DBCC DBINFO
From my testing DB:
DBINFO @0x00000000127BE060
dbi_dbid = 10 dbi_status = 65536 dbi_nextid = 1520060501
dbi_dbname = Testing dbi_maxDbTimestamp = 18000 dbi_version = 655
dbi_createVersion = 611 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2009-01-03 12:58:31.990
dbi_filegeneration = 0
dbi_checkptLSN
dbi_version is 655, so it's a SQL 2008 database (though that I know because it's attached to a SQL 2008 instance).
dbi_createVersion is 611, so it was created on SQL 2005.
I don't have any older databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2012 at 2:19 pm
awesome info Gail;
my db in question had dbi_createVersion = 539 in the code, which your link showed me was SQL 2000, sure enough.
Thank you very much! I added that to my snippets, for sure!
Lowell
April 26, 2012 at 2:52 pm
It's a good idea to make sure Data Purity was run cleanly since the upgrade. The info is on the same page Gail pointed you to 😉
CHECKDB From Every Angle: How to tell if data purity checks will be run?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 3:00 pm
opc.three (4/26/2012)
It's a good idea to make sure Data Purity was run cleanly since the upgrade. The info is on the same page Gail pointed you to 😉CHECKDB From Every Angle: How to tell if data purity checks will be run?
what the question really was, was we've been carrying a "perfect" database around for a long time...it started with the original "version 1.0" schema featuring empty tables except for default data, like lookups and status tables and the like.
It's gone thru so many evolutions..from version 1 to version 11, with lots of minor versions in between, noone could really say if it was an original database, or scripted from Team Foundation Server's scripts we keep.
the question was where it originally came from(ie SQL 2000) , or if it was created via a script for 2005 and upgraded from that script to today.
good day for me, actually...learned or relearned something, if i knew that before.
Lowell
April 26, 2012 at 3:20 pm
Just looking out in case you were now supporting a database that originated on 2000 and had some impure data. I inherited a bunch of 2000 DBs a while back that are now 2005 and they had never had the purity checks run. I just recognized the page-info Gail posted and thought I would pass it along 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply