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)
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 . Screenshot from database property is below
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.
Leave a Reply