Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Needless to say, full backups are imperative. Not only that, you should be able to guarantee a full system restore. –  user3914 Jan 13 '10 at 8:45
    
Thanks Randolph - would you do then do a backup and restore form 2005 and into 2008 or detach/attach? Does it make any difference? –  Nils Jan 13 '10 at 8:56
    
My unproved guess is: detach/attach is faster than backup/restore –  Scoregraphic Jan 13 '10 at 11:14
    
I would never detach a database if I'm upgrading. I always do backup and restore. I don't have a particular reason for doing so, but I prefer to keep the transaction log and DB together in one file, and I personally feel that it's "safer". –  user3914 Jan 13 '10 at 13:38
add comment

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.

share|improve this answer
    
All good points. An intermediate server would be better. –  user3914 Jan 13 '10 at 13:40
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.