You install an instance of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, or of Microsoft SQL Server 2008 R2.
The instance of SQL Server is named INST1 and hosts a database that is named Test_RO_FG_DB.
The database contains the following file groups:
The filegroup that is named RO_FG is marked as READ_ONLY.
You install a new instance of Microsoft SQL Server 2012. This instance of SQL Server 2012 is named INST2.
You detach the Test_RO_FG_DB database from INST1.
You try to attach the Test_RO_FG_DB database to INST2.
You receive an error message that resembles the following:
Msg 3415, Level 16, State 2, Line 1 Database 'Test_RO_FG_DB' 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.
You try to reattach the Test_RO_FG_DB database to INST1.
In this scenario, you cannot reattach the database to INST1. And, you receive the following error message in the SQL Server error log:
2012-05-03 22:55:45.37 spid52 Starting up database 'Test_RO_FG_DB'. 2012-05-03 22:55:45.78 spid52 * ******************************************************************************* 2012-05-03 22:55:45.78 spid52 * BEGIN STACK DUMP: 2012-05-03 22:55:45.78 spid52 * 05/03/12 22:55:45 spid 52 2012-05-03 22:55:45.78 spid52 * Location: logscan.cpp:1490 2012-05-03 22:55:45.78 spid52 * Expression: FALSE 2012-05-03 22:55:45.78 spid52 * SPID: 52 2012-05-03 22:55:45.78 spid52 * Process ID: 9156 2012-05-03 22:55:45.78 spid52 * Description: Invalid switch value 2012-05-03 22:55:45.78 spid52 * Input Buffer 98 bytes - 2012-05-03 22:55:45.78 spid52 * alter database Test_RO_FG_DB set online 2012-05-03 22:55:51.05 spid52 Error: 17065, Severity: 16, State: 1. 2012-05-03 22:55:51.05 spid52 SQL Server Assertion: File: <logscan.cpp>, line = 1490 Failed Assertion = 'FALSE' Invalid switch value. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted. 2012-05-03 22:55:51.10 spid52 Error: 3624, Severity: 20, State: 1. 2012-05-03 22:55:51.10 spid52 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. 2012-05-03 22:56:09.16 spid52 Error: 3414, Severity: 21, State: 1. 2012-05-03 22:56:09.16 spid52 An error occurred during recovery, preventing the database 'Test_RO_FG_DB' (database ID 19) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. 2012-05-03 22:56:09.18 spid52 Error: 928, Severity: 20, State: 1. 2012-05-03 22:56:09.18 spid52 During upgrade, database raised exception 926, severity 14, state 1, address 0000000000F6A971. Use the exception number to determine the cause.
Note This issue only occurs when you try attach a database that contains a filegroup that is marked READ_ONLY. This issue does not occur when you try to move a READ_ONLY database in which all the data is marked READ_ONLY.
This issue occurs because SQL Server 2012 does not detect the read-only filegroup before it starts to upgrade the database. After the upgrade has started, SQL Server 2012 writes entries to the transaction log. Earlier versions cannot read the new transaction log entries.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Cumulative update information
SQL Server 2012
The fix for this issue was first released in Cumulative Update 2 for SQL Server 2012. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2703275 Cumulative update package 2 for SQL Server 2012
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released
You must apply a SQL Server 2012 hotfix to an installation of SQL Server 2012.
To work around this issue, use one of the following methods.
Restore a backup of the database from INST1 on INST2.
Note The issue that is described in the "Symptoms" section does not occur in SQL Server 2012 when you restore a backup from an earlier version.
Perform an in-place upgrade of the earlier version of SQL Server to SQL Server 2012.
Move a database that contains a read-only filegroup to an instance of SQL Server 2012. To do this, follow these steps.
Note Perform steps 4 through 11 on the server that is running SQL Server 2012. For example, perform steps 4 through 11 on INST2.
On INST1, detach the database. For example, detach the Test_RO_FG_DB database.
Move the database files to the server that hosts the INST2 instance.
Try to attach the database to INST2. The following sample code shows how to do this:
CREATE DATABASE [Test_RO_FG_DB] ON PRIMARY ( NAME = N'Test_RO_FG', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test_RO_FG.mdf' ), FILEGROUP [RO_FG] ( NAME = N'Test_RO_FG_File1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test_RO_FG_File1.ndf' ), FILEGROUP [RW_FG] ( NAME = N'Test_RW_FG_File1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test_RW_FG_File1.ndf' )LOG ON ( NAME = N'Test_RO_FG_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test_RO_FG_log.ldf' )FOR ATTACH;GO
Note You will receive the 3425 error message that is mentioned in the "Symptoms" section.
At a command prompt, rename the database files. The following sample command shows how to do this:
Set the database to EMERGENCY mode, and perform a repair. To do this, run the following command.
Note The database transaction logs are rebuilt during this step. This may result in data loss. Therefore, we recommend that you back up the database before you perform this step.
ALTER DATABASE Test_RO_FG_DB SET EMERGENCYGOALTER DATABASE Test_RO_FG_DB SET SINGLE_USERGODBCC CHECKDB (Test_RO_FG_DB, repair_allow_data_loss) WITH ALL_ERRORMSGSGOALTER DATABASE Test_RO_FG_DB SET MULTI_USERGO
Verify that the database is online, and reestablish the Service Broker functionality.
Delete the database files that are not needed. The following sample command shows how to do this:
del /P new_Test_RO_FG.mdfdel /P new_Test_RO_FG_File1.ndfdel /P new_Test_RW_FG_File1.ndfdel /P new_Test_RO_FG_log.ldf
There are several steps that occur when a database is attached to an instance of SQL Server. These steps include recovering the database and upgrading the files from earlier versions of SQL Server.
In the issue that is described in the "Symptoms" section, SQL Server 2012 begins the upgrade process before the read-only files in the database are detected. The upgrade steps include starting a transaction to clear the "cleanly shut down" bit in the boot page of the database. Earlier versions of SQL Server cannot read the begin transaction record. Therefore, the database is not usable in earlier versions of SQL Server, and SQL Server generates the 3624 error.
In-place upgrades when a database is marked as read-only
When you perform an in-place upgrade of an instance of SQL Server that contains a read-only database that is named Test_RO_DB to SQL Server 2012, you may receive error messages that resemble the following in the SQL Server error log:
2012-05-04 21:03:59.23 spid19s Starting up database 'Test_RO_DB'. 2012-05-04 21:03:59.56 spid19s Converting database 'Test_RO_DB' from version 661 to the current version 706. 2012-05-04 21:03:59.56 spid19s Error: 928, Severity: 20, State: 1. 2012-05-04 21:03:59.56 spid19s During upgrade, database raised exception 3415, severity 16, state 1, address 000007FEE66D784A. Use the exception number to determine the cause. 2012-05-04 21:03:59.61 spid19s Error: 3415, Severity: 16, State: 1. 2012-05-04 21:03:59.61 spid19s Database 'Test_RO_DB' 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.
At the end of the upgrade process, the Test_RO_DB database will be in the RECOVERY_PENDING state. You must use the ALTER DATABASE command to set the database to READ_WRITE. Then use the ALTER DATABASE command to set the database to READ_ONLY. This lets the SQL Server engine upgrade the database to the correct version.
In-place upgrades when a read/write database contains file groups that are marked as read-only
When you perform an in-place upgrade to SQL Server 2012, you may receive messages that resemble the following in the SQL Server error log. This issue occurs when the earlier instance of SQL Server hosts a read/write database and contains file groups that are marked READ_ONLY. However, the upgrade process finishes as expected, and the database starts online.
Note In the following error message, the database is named Test_RO_FG:
2012-05-04 21:03:59.23 spid18s Starting up database 'Test_RO_FG'. 2012-05-04 21:03:59.71 spid18s Converting database 'Test_RO_FG' from version 661 to the current version 706. 2012-05-04 21:03:59.71 spid18s Database 'Test_RO_FG' running the upgrade step from version 661 to version 668.