i want restore a sql200db to sql2005.

Question

i want restore a sql200db to sql2005.

charipg

SSCrazy Eights

Points: 9888
More actions
September 11, 2009 at 1:19 pm

#138012

i want restore a sql200db to sql2005.
what are the steps?

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Restore the database, just like you would in any other case, so far as I know.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

charipg SSCrazy Eights Points: 9888 More actions · Answer 2

i want to restore a sql2000db to sql2005.

should take any prerequisites?

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 3

do you have on this new SQL 2005 all "server objects" you need ?

- logins

- jobs

- linked server

...

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

charipg (9/11/2009)
i want to restore a sql2000db to sql2005.
should take any prerequisites?

Nothing much really..

You do have some post-requisites that you need to follow once after restoring to SQL 2005...

You can go ahead and restore it to the SQL 2005

ravikanth. Hall of Fame Points: 3472 More actions · Answer 5

ravikanth.

Hall of Fame

Points: 3472

September 13, 2009 at 5:58 pm

#1052897

Rebuild index and Update stats

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

From a previous forum post on the same question (by another individual), but my answer to that question at:http://www.sqlservercentral.com/Forums/Topic786999-149-1.aspx

I strongly suggest that you use Books On Line starting with:
Backward Compatibility
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm
Reading each subject listed on that page, paying particular attention to the fact that your can not utilize many of the features of 2005 without altering the compatibility level of what was your SQL 2000 db. Further strongly suggest doing this in a test environment, NOT in a production environment. When you fully understand and have corrected any problems encountered consider moving to production.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 7

One thing you will probably want to do after the upgrade is to run DBCC CHECKDB WITH DATA_PURITY.

http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-How-to-tell-if-data-purity-checks-will-be-run.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Micrososoft has an Upgrade Advisor that will check your 2000 database to see if it has any deprecated code, so you can fix it before there are errors in 2005 database. I'd strongly suggest running that first. After that, as everyone else has said, run the restore, change the compatibility setting. I'd suggest updating statistics too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 9

I know it sounds silly, but if your database is for a 3rd party application, check support for it. We have a database we're migrating from 2000 to 2005 but the documentation from the company that provided it says to make sure it stays in compatibility mode 80 (i.e. stay in 2000 mode) which I do not understand, but that's something you need to be aware of as well.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein