Migrating SQL 2000 Database to SQL 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Migrating SQL 2000 Database to SQL 2005

Author

Topic

MichelleMA
Starting Member

32 Posts

Posted - 2007-05-23 : 15:35:52

Hi,
I have a database on a SQL Server 2000, and would like to migrate it to a new server machine that has SQL Server 2005. has anyone already tried this, and can you please provide the steps to follow.

Thank you.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 15:50:04

Try the upgrade advisor: http://blogs.msdn.com/upgradeadvisor/default.aspx
Also, Check this: http://www.sqlservercentral.com/columnists/rpearl/installingandupgradingtosqlserver2005.asp

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MichelleMA
Starting Member

32 Posts

Posted - 2007-05-23 : 17:45:50

Thanks Dinakar. The 2nd link is an article regarding Upgrading In-Place. however, I wanted information on how to migrate a 2000 database to a new hardware box with SQL 2005 after runing the upgrade advisor. Is is a straight detach - copy files - attach ?

Thanks,
Michelle

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 17:54:27

Yes it is a detach/copy files/attach. But there are some steps you need to do post attach.
(1) Change the compatibility level to 90
(2) Reindex ALL the tables
(3) Update Statistics on ALL tables with full scan.

Apart from these, there are probably server level settings that you need to tweak or turn on/off dependong on what features you are using. For Example, xp_cmdshell is off by default. There is a SAC (Surface Area Configuration) under Programs -> MS SQL Server 2005 -> Configuration tools -> SAC. Check that out.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 17:55:21

Ofcourse, you will keep a backup of the files right?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MichelleMA
Starting Member

32 Posts

Posted - 2007-05-23 : 18:04:10

Thanks Dinakar. Yes, I did backup to multiple locations, and made sure the data is good. I will look at the above steps/settings you provided.

Thank you!
Michelle

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-23 : 18:11:47

I would also like to point you to this feedback: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19622

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MichelleMA
Starting Member

32 Posts

Posted - 2007-05-23 : 18:49:51

I'll look at it. Thank you :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources