Readonly database restoration failed

In one of my project I was working on SQL Server 2000 to SQL Server 2008 upgradation. It’s a side by side upgrade so I need to restore the database from SQL Server 2000 to SQL Server 2008, in which some of the databases are readonly. I know I will be able to restore readonly database however in this case when I tried to restore the database in SQL Server 2008 I got the error below

System.Data.SqlClient.SqlError: Database ‘RODatabase’ cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery. (Microsoft.SqlServer.Smo)

readonly_1

At initial I thought it’s because of readonly property of the database as in this case I’m upgrading the database from SQL Server 2000 to SQL Server 2008 so in the backend it has to upgrade the internal physical structure of the database. So I changed the readonly property of the database to FALSE and took a fresh backup and restored but still I got the same error. After troubleshooting further I found that it’s because of the readonly filegroup in that readonly database. I wasn’t aware of this, I was in a assumption that when you restore the readonly db SQL will upgrade the internal physical structure automatically but I was wrong Sad smile. Screenshot from database property is below

readonly_2

Aaron has already posted this in Microsoft Connect for permanent fix however it’s closed. Like readonly database we should be able to restore the database which has readonly filegroups, hope this will be taken care in the future versions. In simple when you upgrade your database the internal structures differs from SQL 2000 to SQL 2008 so it has to upgrade the internal structure. The weird is, it can handle readonly database but not a readonly filegroups or files so to resolve this you need to change this property to FALSE for FG’s or files and then take a backup, now you will be able to restore it.

One thought on “Readonly database restoration failed”

  1. try to reset the database to default through unchecking in read only through gui or t-sql and try upgradation….

    for further info refer to BOL

Leave a Reply

Your email address will not be published. Required fields are marked *

*