locked
Migrating Databases from SQL 2000 to 2008 RRS feed

  • Question

  •  

    We are upgrading our application from SQL 2000 to SQL 2008. We are leaving our databases at their current level (80) and not converting to level 90. We installed SQL 2008 as a separate instance alongside 2000. The hope was that even if we switched to 2008 for a number of days or weeks and then discovered something that we couldn’t fix quickly, we could switch back without losing the changes made during that time, and without having to revert to the backup made before the switch thereby losing all database changes made during the interim. I was able to detach my databases from 2000 and attach to 2008. Then, as a test, I tried to detach from 2008 and attach back to 2000. When I do so, I get the message:

     

    Error 5123: CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘filename.mdf’.

     

    Is there something that gets changed in the database when you attach to 2008 (even when not converting to level 90) that prevents going back to 2000? Is this supported?

    Monday, February 16, 2009 3:15 PM

Answers

  • This is Not supported.
    You cannot attach a 2005 0r 2008  Databases to 2000.
    This process is only One way(you can attch a 2000 database to 2005 or 2008..but not the other way.

    Monday, February 16, 2009 4:24 PM

All replies

  • This is Not supported.
    You cannot attach a 2005 0r 2008  Databases to 2000.
    This process is only One way(you can attch a 2000 database to 2005 or 2008..but not the other way.

    Monday, February 16, 2009 4:24 PM
  • You cannot "downgrade" a file from verison 2008 to 2000.  The compatiblity level has nothing to do with this.  The compatibility level only controls how the query engine treats some queries.

    The only way to do this, would be to bcp out all the tables, and reimport them into 2000. 
    Monday, February 16, 2009 5:42 PM