Question: We recently tried to attach a SQL Server 2008 R2 database to SQL Server 2012 and the upgrade failed because one of the filegroups in the database is set to read-only. When we tried to attach the database back to SQL Server 2008 R2 that failed as well. What did we do wrong?
Answer: The simple answer is that upgrade does not work with read-only filegroups.
I replicated the scenario described above and found that SQL Server 2012 leaves the database in an unusable state. If you try to take a SQL Server 2008R2 database with a read-only filegroup and attach it to SQL Server 2012, you’ll see the errors below:
Starting up database 'paulsdb'.
Recovery is writing a checkpoint in database 'paulsdb' (5). This is an informational message only. No user action is required.
Database 'paulsdb' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.
However, trying to then attach the database back to SQL Server 2008R2 generates the following error:
Could not open new database 'paulsdb'. CREATE DATABASE is aborted.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
The database is now completely unusable. The problem is that SQL Server 2012 wrote into the primary filegroup of the database which means it is now unusable on SQL Server 2008R2. I consider this a bug.
Usually, I would say that the best way to do the upgrade operation is to restore from a backup, or to take a copy of the database rather than trying to attach the only copy of it, as this makes sure that you have a working copy of the database attached to a SQL Server instance if something goes wrong with the upgrade. However, when you start to consider multi-terabyte databases this can become unfeasible very quickly so sometimes there is no choice but to use the real database (as long as you have a backup of it, just in case something goes wrong).
The reason that SQL Server does not allow an upgrade to progress if a database has one or more read-only filegroups is that it is unable to upgrade the physical structures of the read-only filegroup. This would mean the database engine having to copy with two (or more) versions of the physical structures – which is very complicated from an engineering perspective.
Bottom line: Whether you agree with it or not, SQL Server cannot upgrade a database that is partially read-only. And be very careful with how you do your upgrades to SQL Server 2012 as you don’t want to get bitten by the behavior described above.
Update: There’s now a KB article that describes the fix in 2012 RTM CU2.
