Migrating SQL 2000 to SQL 2005

[Oonz]

Hi Friends,
Is there any tool or assistant to upgrade SQL 2000 to SQL 2005.

Thanks,
Arunkumar

[jlaustill]

On Jun 21, 5:53 am, Oonz <[e-mail]:

Hi Friends,
Is there any tool or assistant to upgrade SQL 2000 to SQL 2005.

Thanks,
Arunkumar

Hey Arunkumar,

You don't really need a special tool for this task. You just need to
either make a backup of your database and restore it on the sql 2005
box, or do a dts database transfer. Depending on the size of the
database doing the backup route is generally the easier path. If you
need it to be on the same machine, you can take backups of all your
databases, uninstall sql 2000, install 2005, then just restore all the
databases. You will have a few minor problems to work out, such as
orphaned users, which can be a real pain if you have thousands of
them(been there myself). But for the most part everything will work
just fine.

I would recommend that if you've never done it before that you try it
on a test box before you attempt it live, especially if your data is
important at all :) Have a good go of it man.

Joshua

[Erland Sommarskog]

Oonz (ar******@gmail.com) writes:

Is there any tool or assistant to upgrade SQL 2000 to SQL 2005.

There is an Upgrade Advisor that can run on your SQL 2000 machine and
warn you for things you may need to address. But don't expect it cover
each every small detail. You need to test your applications on your
own as well.

As for the upgrades, there are two ways to go: 1) Upgrade the existing
installation. 2) Install a new instance of SQL 2005 on the same machine
or a different one, and move databases either by BACKUP/RESTORE or
ATTACH/DETACH.

The second method means that you get the regular hassle with login-user
mapping when you restore, although if you use the Copy Database Wizard
in SQL 2005, it can do this for you. Just be sure that you run the
Attach/Detatch method. Don't use the SMO method, which is unrelible.

Another drawback is that you may need more disk space, and maybe an
extra machine.

But apart from that, I think installing a parallel server is a superior
method to an in-place upgrade. By keeping the old server, you have a
benchmark to compare with, if you run into performance problems, as there
may always be some query that the optimizer now makes a bad judgement on.

By the way, when you upgraded either way, remember to run sp_updatestats
on all databases, as the statistics are invalidated when you upgrade.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx