computed column "Version" from table sysdatabases

Question

computed column "Version" from table sysdatabases

river1

SSC-Insane

Points: 24307
More actions
July 15, 2010 at 4:19 am

#97234

Hi,
i need to change the value of the computed column "Version" from table sysdatabases, but as it is a computed column, what i need to do is change the columns that can be changed so this column can be afected, because i can not change the value of a computed column directly.
Anyone knows the formula of this colum "version"?
Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You should not even be thinking of changing the system tables. It's quite likely to cause more damage than it fixes.

If this is about that corruption case from a few days ago, it's not going to help. The data file is damaged, the version is read out of the database header page.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Gail, it's just in my test computer.

It's not in production enviroment the goal of this is try to update this column to the state 539 because the database is with the value 0 and so i can not recover the database, it's just a test to see if i can fix the database...

An a very important test, because if i can fix it, maybe i can recover some damaged databases.

Lowell SSC Guru Points: 323506 More actions · Answer 3

also i'm under the impression that verswion is more tied to the SQL the db is restored on...i took my SandBox from 2005, restored it on my sql 2008, and the verison changed from 611 to 655:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Yes, but when i try to make DBCC checkdb or any other command against this database i always receive the same error:

Cannot open database 'SGCT' version 0. Upgrade the database to the latest version.

That's why i am trying to change directly on the sysdatabases table.

Does anyone know the formula to do this?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Lowell (7/15/2010)
also i'm under the impression that verswion is more tied to the SQL the db is restored on...

It's both. A SQL 2005 instance can only work with databases of internal version 611. If a DB is restored or attached with a lower version, the database is upgraded to 611. So all databases attached to a SQL 2005 instance will be version 611, because SQL will upgrade any that are of a lower version and fail to attach/restore any of higher.

The important thing is that the version is stored in the database's boot page. That's where SQL gets the version from initially. There's no SQL release that has a version of 0, hence one can conclude that the database boot page is likely damaged.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

river1 SSC-Insane Points: 24307 More actions · Answer 6

Hum.... Yap.

Do you have any idea Gail of how i can recover or change the boot page of this database?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You can't.

In theory, if you have a complete understanding of the page structure of the database file and you know exactly how the version code is stored on the database page, exactly what byte offset and binary encoding, you could take a hex editor and edit the database page.

There are maybe 8 people in the world who know enough about the SQL page structure and file structure to do that, and all but one work for Microsoft.

Short answer: You cannot fix the header page, the database file is toast.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass