Upgrading SQL 2000 database to 2005

Question

Upgrading SQL 2000 database to 2005

imarchenko-236376

SSCommitted

Points: 1734
More actions
December 23, 2005 at 5:26 pm

#111583

Hello!

We have got a multi-terabyte database that we would like to move to SQL Server 2005 64 bit server. Given the size of database, I was wondering what would be the most efficient way of converting our database into SQL 2005 format. Can we simply restore SQL 2000 backup on SQL 2005 box? What are the options that we have. If some one had upgraded database of this size, could you please give us some rough time estimate on how long it took.

Thanks,
Igor
December 26, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Matt Breedlove-274818 Newbie Points: 1 More actions · Answer 1

Are you using Imceda's Litespeed. That is what we are doing. We backup the 2000 DB with LS which results in dramatically smaller backup file size and then restore with LS on SQL2005.

I could never get the database copy to work between different vers...

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 2

We are using Idera SQL Safe. I am checking with vendor to see whether their version will work with SQL Server 2005.

Thanks,

Igor

Hobo Long Right there with Babe Points: 733 More actions · Answer 3

Igor,

Idera has released a new version that works on both SQL Server 2000 and 2005 (and they added native 64bit). We're running SQL Safe in both 2000 and 2005 (32bit and 64bit) platforms and it's working great.

mannadba SSC Eights! Points: 989 More actions · Answer 4

There are other backup products as well. Check out this comparison review which evaluates several leading products.

http://www.sqlservercentral.com/columnists/wbrown/fourofakindbackupsoftwareshootout.asp

You can also detach the database, get it to the new server, and re-attach it. SQL Server 2005 will automatically upgrade it. If you have a very fast network or fast, big, removable media, that might be feasible. Otherwise the compression these programs provide will be your friend.

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 5

We have attached database on SQL 2005 server and pretty happy about the process.

Igor

Christopher Robin SSCommitted Points: 1769 More actions · Answer 6

If you do the "detach-reattach" method, make sure to rebuild the statistics as otherwise the database will still use the 2000 version of the execution plans and etc... Ran across this problem one day on a database of only 20 GB of data. Re-building the statistics made a rather visible difference in day to day operation.

umar iqbal SSCommitted Points: 1703 More actions · Answer 7

Christopher, can we simply deattach and attach from SQL 2000 to SQL 2005? can we do vice versa? I took all user databases backup in SQL 2000. uninstall SQL 2000. Install SQL 2005 . Restore sql 2000 backup. Am i on the right track??

:crazy: :alien:

Umar Iqbal

Christopher Robin SSCommitted Points: 1769 More actions · Answer 8

That should work just fine. Doing the reverse tends to not work at all even though the file headers are supposed to be compatible but from 2000 to 2005 it seems to work just fine.