DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 2710782 - Last Review: November 19, 2012 - Revision: 5.0

On This Page

Symptoms

Consider the following scenario:
  • 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:
    • Primary
    • RO_FG
    • RW_FG
  • 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.

Cause

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.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Resolution

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  (http://support.microsoft.com/kb/2703275/LN/ ) 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  (http://support.microsoft.com/kb/2692828/LN/ ) 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.

Workaround

To work around this issue, use one of the following methods.

Method 1

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.

Method 2

Perform an in-place upgrade of the earlier version of SQL Server to SQL Server 2012.

Method 3

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.
  1. On INST1, detach the database. For example, detach the Test_RO_FG_DB database.
  2. Move the database files to the server that hosts the INST2 instance.
  3. 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.
  4. At a command prompt, rename the database files. The following sample command shows how to do this:
    rename Test_RO_FG.mdf original_Test_RO_FG.mdf
    rename Test_RO_FG_File1.ndf original_Test_RO_FG_File1.ndf
    rename Test_RW_FG_File1.ndf original_Test_RW_FG_File1.ndf
    rename Test_RO_FG_log.ldf original_Test_RO_FG_log.ldf
  5. In SQL Server Management Studio, create a database that has the same name and physical structure as the database that you want to attach. The following sample code shows how to do this:
    CREATE DATABASE [Test_RO_FG_DB] ON PRIMARY ( NAME = N'Test_RO_FG_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test_RO_FG_DB.mdf' , SIZE = 4072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
    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' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
    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' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    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' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
  6. Set the database to offline. To do this, run the following command:
    ALTER DATABASE [Test_RO_FG_DB] SET OFFLINE
    GO
  7. At a command prompt, rename the files in the new database. The following sample command shows how to do this:
    rename Test_RO_FG.mdf new_Test_RO_FG.mdf
    rename Test_RO_FG_File1.ndf new_Test_RO_FG_File1.ndf
    rename Test_RW_FG_File1.ndf new_Test_RW_FG_File1.ndf
    rename Test_RO_FG_log.ldf new_Test_RO_FG_log.ldf
  8. At a command prompt, rename the files in database that you moved in step 2. Rename the files to match the database that you created in step 4. The following sample command shows how to do this:
    rename original_Test_RO_FG.mdf Test_RO_FG.mdf 
    rename original_Test_RO_FG_File1.ndf Test_RO_FG_File1.ndf 
    rename original_Test_RW_FG_File1.ndf Test_RW_FG_File1.ndf 
    rename original_Test_RO_FG_log.ldf Test_RO_FG_log.ldf
  9. Set the database to ONLINE. To do this, run the following command:
    ALTER DATABASE [Test_RO_FG_DB] SET ONLINE
    GO
  10. Verify that the database is online, and reestablish the Service Broker functionality.
  11. Delete the database files that are not needed. The following sample command shows how to do this:
    del /P new_Test_RO_FG.mdf
    del /P new_Test_RO_FG_File1.ndf
    del /P new_Test_RW_FG_File1.ndf
    del /P new_Test_RO_FG_log.ldf
Method 4

Reattach a database that contains a read-only filegroup to the earlier instance of SQL Server. To do this, follow these steps.

Notes
  • The database also contains the new transaction log entries from the failed upgrade.
  • Perform steps 3 through 10 on the server that is running an earlier version of SQL Server. For example, perform steps 3 through 10 on INST1.

  1. Move the database files to the instance of SQL Server that is hosting INST1.
  2. Try to attach the database to INST1. The following sample code shows how to do this:
    CREATE DATABASE [Test_RO_FG_DB] ON PRIMARY ( NAME = N'Test_RO_FG_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Test_RO_FG_DB.mdf' ), 
    FILEGROUP [RO_FG] ( NAME = N'Test_RO_FG_File1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Test_RO_FG_File1.ndf' ), 
    FILEGROUP [RW_FG] ( NAME = N'Test_RW_FG_File1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Test_RW_FG_File1.ndf' )
    LOG ON ( NAME = N'Test_RO_FG_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Test_RO_FG_log.ldf' )
    FOR ATTACH
    GO
    Note You will receive the 3624 error message that is mentioned in the "Symptoms" section. You will also receive an 1813 error message.
  3. At a command prompt, rename the database files on INST1. The following sample command shows how to do this:
    rename Test_RO_FG.mdf original_Test_RO_FG.mdf
    rename Test_RO_FG_File1.ndf original_Test_RO_FG_File1.ndf
    rename Test_RW_FG_File1.ndf original_Test_RW_FG_File1.ndf
    rename Test_RO_FG_log.ldf original_Test_RO_FG_log.ldf
  4. In SQL Server Management Studio, create a database that has the same name and physical structure as the database that you want to attach. The following sample code shows how to do this:
    CREATE DATABASE [Test_RO_FG_DB] ON PRIMARY ( NAME = N'Test_RO_FG_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Test_RO_FG_DB.mdf' , SIZE = 4072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
    FILEGROUP [RO_FG] ( NAME = N'Test_RO_FG_File1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Test_RO_FG_File1.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
    FILEGROUP [RW_FG] ( NAME = N'Test_RW_FG_File1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Test_RW_FG_File1.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    LOG ON ( NAME = N'Test_RO_FG_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Test_RO_FG_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
  5. Set the database to offline. To do this, run the following command:
    ALTER DATABASE [Test_RO_FG_DB] SET OFFLINE
    GO
  6. At a command prompt, rename the files in the new database. The following sample command shows how to do this:
    rename Test_RO_FG.mdf new_Test_RO_FG.mdf
    rename Test_RO_FG_File1.ndf new_Test_RO_FG_File1.ndf
    rename Test_RW_FG_File1.ndf new_Test_RW_FG_File1.ndf
    rename Test_RO_FG_log.ldf new_Test_RO_FG_log.ldf
  7. At a command prompt, rename the files in database that you moved in step 2. Rename the files to match the database that you created in step 4. The following sample command shows how to do this:
    rename original_Test_RO_FG.mdf Test_RO_FG.mdf 
    rename original_Test_RO_FG_File1.ndf Test_RO_FG_File1.ndf 
    rename original_Test_RW_FG_File1.ndf Test_RW_FG_File1.ndf 
    rename original_Test_RO_FG_log.ldf Test_RO_FG_log.ldf
  8. 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 EMERGENCY
    GO
    ALTER DATABASE Test_RO_FG_DB SET SINGLE_USER
    GO
    DBCC CHECKDB (Test_RO_FG_DB, repair_allow_data_loss) WITH ALL_ERRORMSGS
    GO
    ALTER DATABASE Test_RO_FG_DB SET MULTI_USER
    GO
  9. Verify that the database is online, and reestablish the Service Broker functionality.
  10. Delete the database files that are not needed. The following sample command shows how to do this:
    del /P new_Test_RO_FG.mdf
    del /P new_Test_RO_FG_File1.ndf
    del /P new_Test_RW_FG_File1.ndf
    del /P new_Test_RO_FG_log.ldf

More information

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.




Applies to
  • Microsoft SQL Server 2012 Enterprise
Keywords: 
kbtshoot kbsurveynew kbprb KB2710782
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support