January 9, 2009 at 11:51 am
We are building a new data center with new servers. I'd like to take this opportunity to upgrade to 2005 but have a few questions. We have 3 servers on 2000, one with a 900 Gig database, and a few 150+ Gig databases. They don't have anything complex on them and Upgrade Advisor doesn't show much to worry about. My main questions:
I plan to:
Install SQL 2005 on the new servers.
Detach & move the user databases onto the 2005 servers.
How do I bring over the system databases into 2005 ?
I'd like to avoid having to script out much except as a failsafe backup. We have lots of convoluted logins, Linked Servers, DTS packages (can run in Legacy mode), no replication, no maint plans.
What's the best method or suggestions for my scenario ?
January 9, 2009 at 12:14 pm
Use sp_helprevlogin to move the logins.
Backup the databases and restore on the 2005 servers.
Once a database has been on 2005 you cannot get it back to 2000, so to prevent issues you can't recover from do the backup/restore, this way you always have a way back.
January 9, 2009 at 12:16 pm
You can't restore SQL 2000 system databases onto SQL 2005. Won't work. You'll need to migrate your logins, etc., but there are processes for doing all of that. If you really don't want to do that, I recommend an in-place restore (after a verified backup), and then move that to the new servers.
- 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
January 12, 2009 at 7:14 am
GSquared (1/9/2009)
You can't restore SQL 2000 system databases onto SQL 2005. Won't work. You'll need to migrate your logins, etc., but there are processes for doing all of that. If you really don't want to do that, I recommend an in-place restore (after a verified backup), and then move that to the new servers.
Sorry, "in-place upgrade" is what that should have read.
- 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply