Can a sql 2000 database be moved to sql 2005 but remain in SQL 2000 format?

  • Juanita

    SSCertifiable

    Points: 5300

    HI,

    We are trying to move an application's database from sql2000 to sql2005. The vendor is asking if the database can remain in SQL 2000 format?

    I've never been asked this so not sure what the answer is. Would this have to do with leaving the compatibility level at 80?

    Thanks a bunch in advance for any help!

    Juanita

  • Lynn Pettis

    SSC Guru

    Points: 442359

    Yes, you can leave it in compatibility mode 80, but once the database is attached to SQL Server 2005, you won't be able to take it back to a SQL Server 2000 server as the internal structure will be updated.

  • Juanita

    SSCertifiable

    Points: 5300

    HI Lynn,

    So by keeping the level to 80 this is considered 'not converting' to sql 2005?

    Thanks,

    Juanita

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    That is generally a true statement, you won't be able to do a DB backup and restore back on a 2000 box, but it will generally behave the same.

    I would recommend reading the SQL Server 2005 BOL (Nov 2008) article:

    sp_dbcmptlevel (Transact-SQL)

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm

    It covers some things to think about..

    CEWII

  • Juanita

    SSCertifiable

    Points: 5300

    Thanks a bunch Elliot and Lynn...

    This helps alot!

    Juanita

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    You are welcome.

    CEWII

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    Lynn Pettis (7/1/2009)


    Yes, you can leave it in compatibility mode 80, but once the database is attached to SQL Server 2005, you won't be able to take it back to a SQL Server 2000 server as the internal structure will be updated.

    Nope, this is still considered as upgrading to SQL Server 2005. Compatibility mode just means that newer functionality available in 2005 will not be available, and older functionality that works in SQL Server 2000 will probably still work.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Juanita

    SSCertifiable

    Points: 5300

    Jeffrey

    Are you saying that once you restore to a sql 2005 server, your database is actually 'converted' at that time to sql2005?

    Juanita

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    Yes, exactly - once you restore or attach a 2000 database to a 2005 instance, that database is converted to 2005. You cannot then later backup/restore that database to a 2000 instance, nor can you detach/attach that database back to a 2000 instance.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Juanita

    SSCertifiable

    Points: 5300

    Thank you very much Jeffrey!

    Juanita

  • SUBRAHMANYA HEDGE

    SSCommitted

    Points: 1514

    In case if you require to do a rollback of your database from SQL 2005 to SQL 2000 you can follow these tedious steps:

    - Script out entire database including all the objects before converting to SQL 2005 format.

    - When you need to rollback run this script and create the new blank database.

    - Then using DTS, import the data. But this needs to be carried out considering all your constraints...

  • Juanita

    SSCertifiable

    Points: 5300

    Thanks!

    Juanita

  • steve block

    SSCertifiable

    Points: 7698

    Another thought that the original user might be interested in:

    use Sql Server 2005 front end tools (SSMS, etc.) against a SS-2000 database.

    I'm not fully sure of the value but that might be something that they would be interested in.

    Steve

Viewing 13 posts - 1 through 13 (of 13 total)

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