July 24, 2006 at 8:03 am
I am preparing to upgrade several of our SQL Server 7 databases to 2005 and am unsure of the best approach. The options I am considering are:
1. Use the DB Upgrade Wizard to upgrade to SQL 2000 and then use the wizard to upgrade from 2000 to 2005.
2. Simply take a backup of the SQL 7 database and restore to SQL 2005.
3. Create a new blank SQL 2005 database, script out the SQL 7 database and created it from these. Then import the data.
I am also unsure whether to leave the compatibility level at 7 or put it up to 9. I know there will be likely issues to be resoved if I put it to level 9, but will the long term benefits we worth doing it? I also have concerns about running databases with different collations, which is why option 3 is in there.
All 3 options will involve using the Upgrade Advisor first to identify any glaring issues. I have discounted an in-place upgrade due to operational issues.
If anyone has had any experience of this or have any views on what would be the best approach, I would be grateful to hear them.
Many thanks
Graham
July 24, 2006 at 11:43 pm
Why not sp_detach_db from Vs7 and sp_attach_db to 2005 - database will be automatically updated to new version during attachment. Also easy to test with copy.
July 26, 2006 at 1:34 am
Thanks Barry, I will consider this as another option. I wasn't aware that doing this would convert the database up to version 2005, so that is useful. I noticed that the Copy Database Wizard uses detach/attach so I am wondering if this does some additional conversion as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply