Post reply

Attaching SQL Server 2000 database on SQL Server 2005 server

  • January 19, 2006 at 6:14 pm

    #112146

    Hello!
     
        We are planning to migrate our multi terabyte SQL Server 2000 database by detaching data/log files on 2000 box and reattaching them on 2005. Has anyone experienced any issues with this process in general and databases of such size in particular? As far as I know, SQL Server will perform database conversion when attaching database on SQL 2005.
      
     
    Any thoughts are greatly appreciated,
    Igor

  • January 23, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • January 23, 2006 at 8:03 am

    #616452

    I know when you take a SS2000 database and restore it into SQL2005 it keeps it in SQL 2000 compatibility mode. You have to physically change it to SQL 2005 which is compatibility 9 level in order to take advantage of SQL 2005 stuff. I would assume that is the same with detach/attach as well.

  • January 23, 2006 at 9:00 am

    #616475

    Thanks. I already know this. I was wondering if anyone has experienced problems attaching databases of tera byte size.

    Igor

     

  • January 23, 2006 at 9:30 am

    #616478

    Couple days ago we 've done this trick with 4 Tb database- no problems

  • January 23, 2006 at 10:50 am

    #616499

    Thanks Yuri!

  • January 23, 2006 at 5:14 pm

    #616554

    You should have no problems.  However, if you ever want to go the other way (i.e., SQL Server 2005 to SQL Server 2000), you will have to do that with Bulk Copy Program.  It's not a lot of fun.


    All the best,

    Dale

    Author: An Introduction to SQL Server 2005 Management Studio

  • November 4, 2009 at 11:58 pm

    #1075180

    Markus (1/23/2006)

    I know when you take a SS2000 database and restore it into SQL2005 it keeps it in SQL 2000 compatibility mode. You have to physically change it to SQL 2005 which is compatibility 9 level in order to take advantage of SQL 2005 stuff. I would assume that is the same with detach/attach as well.

    hi. Can I have a link regarding the above topic? about this compatibility mode.

    thanks

  • November 5, 2009 at 12:39 am

    #1075191

    You will not face any such problems yet. I migrated 6.5 tbs database yesterday from 2000 to 2005 and just needed to change compatibility mode to 90.

    ensure that you physical files are proper so before detaching run checkdb and full backup and update stats

    do same after attaching

Viewing 9 posts - 1 through 8 (of 8 total)

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