Post reply

Cannot attach sql 2005 database to sql 2008

  • May 31, 2014 at 12:17 pm

    #286030

    Hi,

    I have a strange condition where certain SQL 2005 databases canot be attached to a sql 2008 instance. I could successfully attach a few however i have noticed that the databases where in I cannot attach are where indeed the fileids in the sys catalog are not consecutive. For some reason I have some missing file ids in teh database and hence cannot attach it to the SQL 2008 instance. Other databases where the fileids are consecutive works fine.

    I however get a very generic error as below which states actally file rights or read write file groups. Even if i set all file groups read write it does not work ..I could successfully attach databases with read only filegroups.

    Database 'BODATA' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.

    Any one come across such a situation. Please let meknow. I am fighting against time to fix this issue.

    Thanks,

    Renita

  • May 31, 2014 at 1:20 pm

    #1718033

    It's saying pretty much what the problem is. Go back to 2005 and find the read-only files.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 31, 2014 at 1:23 pm

    #1718035

    Hey Jeff,

    I dont think the problem is with read only files. As i could attach database with read only files.Problem with this database is that I have deleted data in the past and hence the fileids are not consecutive and hence when i attach it on the destination server it cries for different file ids.

    I found this post with exactly the same issue by Paul Randal where he says to create the databse with exactly same file ids but you would need file initialization set. But cannot figure out how to do that.

    Any idea with that?

    http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/

    Thanks,

    Renita

  • May 31, 2014 at 1:29 pm

    #1718037

    Hey,

    this is what the post says..

    Create a dummy database, with the same name, the same number of data and log files, with the same file names and same file IDs (make sure you have instant file initialization enabled so the file creations don’t take ages – see this blog post). This can be tricky to do if you’ve added removed files, but you need to make sure the file IDs are exactly the same.

    Was wondering how do i create a database on my destination server with the same file ids as my source server?

    Thanks,

    Renita

  • June 1, 2014 at 10:22 am

    #1718069

    renitaj (5/31/2014)

    Problem with this database is that I have deleted data in the past and hence the fileids are not consecutive and hence when i attach it on the destination server it cries for different file ids.

    But it's not complaining that the file IDs aren't sequential, it's saying that one or more of the files are read only. Go and check each file, make sure none have the read only flag, make sure none of the directories have the read only flag

    Paul's blog post is specifically for attaching a damaged database (one that was suspect when detached)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 2, 2014 at 3:25 am

    #1718135

    Hey,

    Can you please try running your SSMS as administrator..just a suggestion try..

    🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply