Compatibility Level SQL Server 2000 (80)

Compatibility Level SQL Server 2000 (80)

• SGray

  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

  
  
  

• alexdavidson

  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.

  
  
  

• Jason.J

  Can you be more specific on what does not work (i.e how are you checking or verifying it is not working)

  Thanks,

  Pete

  
  
  

• Ranier

  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.  < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  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

  
  
  

• Biodigit

  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...

  
  

• larimore

  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

  
  

• TFD2001

  Thanks Peter,

  I appriciate it...

  Nigel...

  
  

• Brian Gordon

  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

  
  
  

• Mohit17

  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.
  

• Mike Gough

  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
  
  
• Compatibility Level SQL Server 2000 (80)