A database from an earlier version of SQL Server becomes unusable when you attach it to an instance of SQL Server 2012

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:

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 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.

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:

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:


Özellikler

Makale No: 2710782 - Son İnceleme: 19 Kas 2012 - Düzeltme: 1

Geri bildirim