Moving from SQL 2000 to SQL 2008

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

General SQL Server Forums

New to SQL Server Administration

Moving from SQL 2000 to SQL 2008

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 backup
Restore on Target server, with NORECOVERY

There is no urgency for the above step, and users cans till use the Source database

then prevent access by users to the Source database
Take a final "tail" TLog backup
Copy 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 on

Further 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

Posted - 2011-09-20 : 09:59:24

"How do I tell if I have Patch 4?"

http://www.sqlteam.com/article/sql-server-versions

But I still think you would be better off with Backup / Restore rather than Detach

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources