Copying database from SQL 2000 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)

Copying database from SQL 2000 to SQL 2005

Author

Topic

suzanazb
Starting Member

5 Posts

Posted - 2007-11-07 : 15:04:39

Hi,

I need to copy database from SQL 2000 to SQL 2005 server. I was using the Copy wizard from SQL 2000 Server, I could enter the source server and server where to copy SQL 2005 but when I wanted to select the database to copy, nothing happened. Like I was kicked out, no errors etc.

Any one with idea or information, please let me know ASAP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 15:05:49

Never (well almost never) use the copy wizard to upgrade a database from 2000 to 2005. Your better options are either BACKUP/RESTORE or detach/attach.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

suzanazb
Starting Member

5 Posts

Posted - 2007-11-07 : 15:07:35

Will detach just copy the database or will actually "move" it? I would still like to keep the database on SQL 2000 so that I can test, if everything else will be working.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 15:11:19

You have to copy the files to the new location. If you want to keep a copy of it on 2000, then after the copy of the database files is complete, just attach the original files back to 2000.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

suzanazb
Starting Member

5 Posts

Posted - 2007-11-07 : 15:20:14

Thank you. I never did that this way, is there a step by step guid that I can look into?

Thanks

suzanazb
Starting Member

5 Posts

Posted - 2007-11-07 : 15:22:04

Oh one more thing... I want to copy database from SQL 2000 on Serever1 to SQL 2005 on server2.
It is not real upgrade of the server...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 15:25:41

See SQL Server Books Online for details on detach/attach.

Even if this isn't a real upgrade of the server, the steps are the same.

If you plan on using using 2005 features, make sure to change the compatibility level to 90 for the upgraded database after the attach. MS does not do this for you during the upgrade. It will be at 80 level after the restore or attach.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-07 : 23:18:56

If you can't afford db down on sql2k, use backup/restore instead.

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 00:42:38

For an initial experiement you could just:

Copy the most recent backup from Server1 too Server2

Restore on Server2 to a new database name

Kristen

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 00:43:37

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Migrating+to+SQL+2005+Hints+and+Tips