Home / server / Questions / 102195
Accepted
Nils
Asked: 2010-01-14 00:37:13 +0800 CST

What's the best way to upgrade SQL 7 to SQL 2008?

  • 772

I'm upgrading an old website and moving everything to new servers and latest software versions.

As part of this upgrade, I need to move a database from a Win2000 server running MS-SQL 7 to a new Win2008 64bit server running MS-SQL 2008.

I've tried to do a database backup and restore during an earlier test run, and SQL 2008 won't restore database backups from version 7.

I was thinking that I could try using BCP to export the data and import it into an empty database with the same schema on the 2008 machine.

Otherwise I could try to install SQL 2000 or SQL 2005 on the new box and restore to that - then either:

  • Detach that db and attach it in the sql 2008 engine

OR

  • Backup from that DB and restore in 2008

This is a production system so transferring the data will cause the system to be down for a while, but I'd like to keep this to a minimum.

What would be the best way of approaching this upgrade? Any pitfalls to look out for? Should I use the BCP way or the intermediate db version?

sql-server

3 Answers

  1. Best Answer

    If you want to upgrade, take it through SQL 2000 or SQL 2005, and then up to SQL 2008. There is a lot of documentation on how to do this. Read it before you start. Seriously.

    Your method of transferring structures and data is also an option, but may run into problems. I'd do the upgrade method. Personally though, I'd do it via SQL 2005, and then up to 2008.

    • 3

  2. You can't upgrade from SQL Server 7 to SQL Server 2008 directly. This document lists out the possible supported options. [http://msdn.microsoft.com/en-us/library/ms143393.aspx]

    Your best bet is to upgrade from SQL Server 7 to SQL Server 2000 and from SQL Server 2000 to SQL Server 2008. And don't make too many hops than necessary. This is your best option to get authoritative information. http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en

    After the upgrade

    1. Be ready to have some performance problems as the query optimizer is written from the ground up and you will get completely different plans and be on the lookout for some code re-write. Don't expect that going to SQL Server 2008 will solve the problems (at-least) in the beginning.
    2. Be sure to rebuild all indexes or update stats with fullscan
    3. Run a DBCC UPDATEUSAGE and this will help the system views (DMVs) to correct the rowcounts.
    4. ALTER DATABASE SET PAGE_VERIFY CHECKSUM and this will help you to detect IO corruption early on. Ideally you want to set this option before running rebuilding the indexes.

    There are many more gotchas you may run into, so planning and doing the homework is the key to successful upgrading.

    • 2

  3. I personally would go for the method of restoring on an intermediate server. Copying the structure and data should also work, but may actually take longer than two detach+copy+attach operations.

    If you do go for the BCP copy-structure-and-data approach, make sure that you set the compatibility level of the destination database correctly to reduce issues due to changes in SQL itself. See http://msdn.microsoft.com/en-us/library/ms178653(SQL.90).aspx for more details on those differences.

    Even with compatibility level set accordingly, you should plan for a full regression test of any application using the database as there may still be slight differences where your code relies on "undefined" behavior that was constant in SQL7 but not guaranteed to be so in later editions.

    • 1