SQL 2000 DB in SQL 2005

Question

SQL 2000 DB in SQL 2005

Amit Chaudhary

Ten Centuries

Points: 1050
More actions
November 14, 2007 at 6:24 pm

#180561

Hi,
Is it possible to just copy the .MDF and .LDF files of an SQL Server 2000 Database, and restore them in SQL Server 2005 as new databases ?
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

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

happycat59 One Orange Chip Points: 29386 More actions · Answer 1

Yes - you certainly can. Simply detach from SQL 2000 and then attach to SQL 2005.

A word of warning : this is a one way process. When SQL 2005 opens the database (during the attach process), it makes changes to some of the meta data in the database which means that SQL 2000 cannot open the database.

If you need to use the database using SQL 2000, should copy the MDF/LDF files somewhere safe before attaching to SQL 2005.

If you need to go back to SQL 2000 using data that has been updated by SQL 2005 (ie. the database that was attached to SQL2005) you will need to use DTS or SSIS to copy the data.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

Yes you can detach/attach the database. before that just confirm that there are no active enties in the log file else you need to go for the option of attach_log_rebuild_log.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Amit Chaudhary Ten Centuries Points: 1050 More actions · Answer 3

Hi,

Thanks for the reply.

Do i need to detach the database ? The prb is the SQL 2000 database machine is no longer available to me. I only have the mdf/ldf files of the Databases from that Server.

Thanks again.

happycat59 One Orange Chip Points: 29386 More actions · Answer 4

You should be OK to attach the MDF/LDF files. I would use a copy of them - just in case.

Brandie Tarvin SSC Guru Points: 172991 More actions · Answer 5

The business logic of only attaching copies of the .mdf and .ldf is that if these files get corrupted or set suspect when you're trying to attach them to 2k5, you still have the original files you can go back to.

My advice - Never ever ever attached a 2000 db file directly to 2k5 without making a copy first. Even if you never need the copy, safe is better than sorry. Especially considering you could get fired for "losing" the data because you didn't want to take 5 minutes to make the copy.

CYA is always a good thing. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Amit Chaudhary Ten Centuries Points: 1050 More actions · Answer 6

Amit Chaudhary

Ten Centuries

Points: 1050

November 27, 2007 at 3:49 pm

#754759

Thank You.

Brandie Tarvin SSC Guru Points: 172991 More actions · Answer 7

Glad we could help. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.