database to move from SQL 2000 to SQL 2008 R2 RRS feed

  • Question

  • We are migrating an old application database from SQL 2000 to SQL 2008R2. There are 3 basic approaches that I can think of.

    Take backup & restore on new server
    Detach and Attach database file
    Create empty database structure, and then pump data from old database to new one with SSIS

    http://msdn.microsoft.com/en-us/library/ms189625.aspx says when you attach or restore 2000 data file on 2008 R2 it automatically upgrades it, which sounds good and later compatibility level can be changed to SQL 2008. One argument against this approach was it keep the data as fragmented as it was in the existing system. Whereas if we copy the data to empty tables it will be much more organised and less space consuming.

    My question is, does this argument has any weight or will it really benefit copying the data?

    • Moved by Andrew.Wu Monday, January 31, 2011 2:51 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Thursday, January 27, 2011 12:11 PM


  • Personally, I wouldn't consider source database fragmentation in determining your upgrade method.  I think it would be better to establish a maintenance plan after the upgrade that regularly rebuilds/defrags indexes.  Also, I suggest you start with the native 100 compatibility unless you specificaly need to change it as a workaround until application code is remediated.  You can always change it back to a earler version if needed.

    An important task following an upgrade using restore or attach is to update statistics.  You might consider rebuilding indexes immediately after an upgrade via restore so that indexes will be defragmented with updated stats.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by WeiLin Qiao Monday, February 7, 2011 11:20 AM
    Thursday, January 27, 2011 1:21 PM