locked
Compatibility Level SQL Server 2000 (80) RRS feed

  • Question

  • Hallo Everyone,

    I have an SQL database that I need to detach from an SQL2005 server and reattach to an SQL 2000 database. I tried to set the Compatibility level from SQL Server 2005 (90) to SQL Server 2000 (80). This did not work

    Any ideas?

    Nigel...

    Friday, January 6, 2006 7:50 AM

Answers

  • Hi Peter,

    I have a situation where I'm running a piece of software on SQL2005 and I need to give a backup of the sql database for that software to someone who is running SQL2000, is there any way that I can take a backup, or basically get them to be able to read this database on their SQL2000 setup?

    Currently when they try to restore a backup I've taken they get an SQL Server error saying: The backed up databas has on-disk structure version 611. The server supports 539 and cannot restore or upgrade this database.

    Any help much appreciated.

    Thanks, Mark.

    Tuesday, July 11, 2006 9:51 AM
  • You can not backup on SQL2005 and restore on SQL2000. However you have other options:

    * Script the database from SQL2005 to SQL2000

    * Use the full version of SQL Server 2005, install as separate instance

    * Use the free SQL Server 2005 Express edition, install as separate instance

    * Creating a linked server from SQL2000 to SQL2005 should work

    Thanks,

    Peter

    Tuesday, July 11, 2006 1:57 PM

All replies

  • How are you trying to do this?  Try setting the compat level to 80 when attached to SQL Server 2005, and then attach to SQL Server 2000.

    Peter

    Friday, January 6, 2006 4:16 PM
  • Hallo Peter,

    This is what I did

    1. Goto SQL 2005
    2. Set the compatability level from 90 to 80
    3. Detach the database from SQL 2005
    4. Goto SQL 2000
    5. Attach the database

    Unfortunately this does not work, any ideas?

    Thanks..

    Nigel...

    Monday, January 9, 2006 7:02 AM
  • Can you be more specific on what does not work (i.e how are you checking or verifying it is not working)?

    Thanks,

    Pete

    Monday, January 9, 2006 6:01 PM
  • I have the same problem. You get an error (don't have it at hand) telling you to repair the database (index problem). As far as I have been able to find out about this matter, it is not possible to go back to SQL Server 2000 once you have attached the database to a SQL Server 2005. You can create a new, empty database and transfer the data afterwards. So the question is: is it at all possible to work with a database under SQL Server 2005 and easily transfer that to SQL Server 2000?

    Thanks for any help on this. If there is no solution, I will have to go back to a SQL Server 2000.
    Monday, January 9, 2006 9:12 PM
  • Sorry, I may have misled you.  It is not possible to move a database from SQL Server 2005 to SQL Server 2000 through attach/detach and backup/restore as SQL Server 2000 does know the structure of the database. 

    Compatibility mode was introduced back in SQL 7 or earlier to keep applications running after upgrading the database to the latest version.  Compatibility mode is all about how T-SQL is parsed and interpreted.  Compatibility mode does not change the structure of the database.

    Why are you looking to move a database from SQL Server 2005 to SQL Server 2000?

    Here are some options for you to consider (there could be more)

    * You can script and transfer the data using SMO (or Copy Database Wizard)

    * You can replicate the data from SQL Server 2005 to SQL Server 2000

    * You can keep the databases in SQL Server 2000 and continue to use the SQL Server 2005 tools.

    * Keep the database in SQL Server 2005 with database compatibility mode at 8 so other applications can continue to access the database.

    Thanks,

    Peter

    Tuesday, January 10, 2006 2:58 PM
  • Thanks. That was exactly my impression. I think this

    * You can keep the databases in SQL Server 2000 and continue to use the SQL Server 2005 tools.

    is what I will do.

    Thanks!
    Patrick
    Tuesday, January 10, 2006 3:09 PM
  • Thanks Peter,

    I appriciate it...

    Nigel...

    Tuesday, January 10, 2006 3:11 PM
  • Hi Peter,

    I have a situation where I'm running a piece of software on SQL2005 and I need to give a backup of the sql database for that software to someone who is running SQL2000, is there any way that I can take a backup, or basically get them to be able to read this database on their SQL2000 setup?

    Currently when they try to restore a backup I've taken they get an SQL Server error saying: The backed up databas has on-disk structure version 611. The server supports 539 and cannot restore or upgrade this database.

    Any help much appreciated.

    Thanks, Mark.

    Tuesday, July 11, 2006 9:51 AM
  • You can not backup on SQL2005 and restore on SQL2000. However you have other options:

    * Script the database from SQL2005 to SQL2000

    * Use the full version of SQL Server 2005, install as separate instance

    * Use the free SQL Server 2005 Express edition, install as separate instance

    * Creating a linked server from SQL2000 to SQL2005 should work

    Thanks,

    Peter

    Tuesday, July 11, 2006 1:57 PM
  • Hi Peter,

    Could you please help me how to figure out my problem:

    I have a database upgraded from SQL 2000 to SQL 2005. Some of the store procedures, functions, triggers do not run in 90 mode. How can I get all the name of these store procs/functions/triggers so that I can manually modify them in order to make it work in my existed application? Do you know any way that make this job faster and lighter?

    Thanks,

    CCC

    Thursday, November 16, 2006 4:20 PM