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?