June 30, 2009 at 10:18 am
We would like to move our data (41GB) from SQL 2000 to a SQL 2005. Is this possible and is there some documentation I can follow step-by-step instructions. We would like to move off the SQL 2000 box to a SQL 2005 box. We are upgrading and moving from one box to another. Hope someone can help.:(
June 30, 2009 at 12:56 pm
I believe that the backup files are compatible that direction.. Meaning you can take a backup file from the 2000 box and do a restore directly on the 2005 or even 2008 box.
Four things to think about though:
1. Logins will not come accross.
2. Keep in mind file locations on the restore, it remembers where they were previously, just update them at restore and it will be fine.
3. Do a shrink of the DB before and be sure to truncate the transaction log, you want that dump to generate files as small as possible.
4. You will need at LEAST twice as much disk space as the size of the backup file, ie: dump file 41GB, you need at LEAST 82GB, and likely much more.
Plan on maybe 10 hours of time to do this, the dump file copy will likely take several hours and the restore will not be quick. Depending on your network situation the dump file may not need to be moved, you *might* be able to do the restore over the network, it will be slow but won't require as much disk space to try it. I do recommend copying the dump file local, mainly because if you have to do the restore more than once, you only have to pay for the copy operation time once.
CEWII
June 30, 2009 at 1:11 pm
You will also want to run the Database Upgrade Advisor against the database. There may be things you need to modify/change before moving to SQL Server 2005, others after the move.
June 30, 2009 at 1:29 pm
You can also use Attach, Detach methods to upgrade. It will save you time taken to restore a database. But it will still be advisable to have backup copy before you detach your database because in case ur database has consistency errors, you dont want to be in a soup of reattaching your database problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply