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?
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.