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.
Author |
Topic |
rocksteer
Yak Posting Veteran
69 Posts |
Posted - 2011-09-20 : 08:16:03
|
How to do it? I have databases as large as 235 GB. Is there a quick way? Seems like sp_detach followed copy to copy to news server, followed by sp_attach would be best. On actual move, I cannot be down much longer than a day. How to transfer the logon info(scripts?)Things I need to know? Issues?I see some articles about going to 2005 then 2008. We don't have 2005. I don't think we would want to do that anyway.Also, I may need to help with the install. We are ordering sql server 2008, and I believe we will be putting it on either Windows 2003 Server or Windows 2008 Server (probably 32 bit). The Sql Server 2000 is currently on Windows 2003 Server (32 bit). |
|
Kristen
Test
22859 Posts |
Posted - 2011-09-20 : 08:44:16
|
"Seems like sp_detach followed copy to copy to news server, followed by sp_attach would be best["We've done large databases in a few 10's of minutes.Full backupRestore on Target server, with NORECOVERYThere is no urgency for the above step, and users cans till use the Source databasethen prevent access by users to the Source database Take a final "tail" TLog backupCopy that to the Target server and Restore, and then use WITH RECOVERY to put the target database live.However, the "WITH RECOVERY" will "upgrade" the database through all the intervening versions, which can take a little while. You also need to rebuild Indexes and Stats, set up Housekeeping jobs, logins (which you can do beforehand) and so onFurther details here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-09-20 : 09:43:14
|
Make sure your SQL Server 2000 is patched to at least SP4, or the attach will not work. N 56°04'39.26"E 12°55'05.63" |
 |
|
rocksteer
Yak Posting Veteran
69 Posts |
Posted - 2011-09-20 : 09:54:11
|
How do I tell if I have Patch 4?@@version returns...Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Also, why do I show Windows NT 5.2 when the operating system is Windows 2003 server? |
 |
|
Kristen
Test
22859 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-09-20 : 17:27:10
|
You still need SP4 for SQL Server 2000 before making your backup, I think. N 56°04'39.26"E 12°55'05.63" |
 |
|
Kristen
Test
22859 Posts |
Posted - 2011-09-21 : 03:00:32
|
Not sure about that as I don't think we had SP4 installed before we upgraded. We might have done though ... I do remember there were performance issues with SP4 and it failed QA tests as a consequence. Can't remember if we fixed the App. or stuck at SP3. |
 |
|
|
|
|
|
|