April 1, 2024 at 11:04 pm
I recently did an in place upgrade from 2014 to 2022, and couldn't connect to any of the databases that were in Read_Only state AND still in 120 compatibility mode, with the following error: "Cannot open database 'DBName' version 782. Upgrade the database to the latest version."
After setting all the databases to 160 mode the databases were accessible again even in Read_Only state however the user had a restore that replaced several databases from other non-2022 servers and set the compatibility to 120 (2014) and set them to Read_Only again. Once the databases were back to 120 & Read_Only, both backups and restores fail with the same access message.
Has anyone else come across this issue? I've not been able to find anything referring to this on the internet. It looks like a bug in 2022, so would like some confirmation before we report it.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 2, 2024 at 8:33 am
Makes sense, the DB needs to undergo the upgrade process from a 2014 format to a 2022 format and if it's read only the upgrade process can't write the changes.
I wouldn't have thought changing the compatibility level would be the one to do it though.
Try it again, but set the DB to read_write, then take it offline then online, to trigger it to upgrade.
And yeah I would expect any backup/restore would fail too as it's not in the right state as it's still a 2014 formatted DB on a 2022 instance.
April 11, 2024 at 2:59 am
This issue is resolved. There are internal upgrade changes made to all the user databases when upgrading to SQL 2022 (I would assume other versions as well), but these don't happen if the DB is Read_Only. Without these changes connections to the Read_Only databases fail.
The solution is simple, you just need to set the DB to READ_WRITE and SQL will immediately apply the internal changes, then you can set the database back to Read_Only. After that connectivity works, regardless of the compatibility level.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 25, 2024 at 12:34 pm
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply