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 |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2008-11-19 : 07:21:48
|
Hello, we'll be upgrading from SQL Server 2000 to SQL Server 2005 very soon. Our new server will be running Microsoft Windows 2003 server and of course SQL Server 2005. The old machine is MS Windows 2000 Server and SQL Server 2000.I wondered if someone could provide useful links which cover migrating databases and security frameworks from SQL Server 2000 to SQL Server 2005. I remember back in the day there was a SPROC one could execute on the old server and then again on the new server that would preserve permissions etc.One question up front: Is is relatively straightforward to detach databases in SQL Server 2000 and then simply re-attach in SQL Server 2005 with the result that the database retains 100% functionality? I realize the security settings, permissions etc are a completely different ballgame, mainly curious about detach, attach at this point in the game.Thanks!  |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-19 : 07:25:51
|
| Better to do with Backup/Restore because1)Less downtime2)If you can't attach to SQL 2005,you can't reverse back and attach to SQL 2000Choose SQL 2000 to SQL 2005 microsoft sproc and it will work. |
 |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2008-11-19 : 08:06:03
|
quote:
Originally posted by sodeep
Choose SQL 2000 to SQL 2005 microsoft sproc and it will work.
thanks. for the SPROC you mention above, does this run the backup/restore procedure you mentioned on the SQL 2005 box? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-19 : 09:59:06
|
| No ,only resolves permissions. |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2008-11-20 : 14:36:52
|
quote:
Originally posted by sodeep
Better to do with Backup/Restore because1)Less downtime2)If you can't attach to SQL 2005,you can't reverse back and attach to SQL 2000Choose SQL 2000 to SQL 2005 microsoft sproc and it will work.
If I detach the MDF on the 2000 box, make two copies, and the 2000-to-2005 attach fails on the 2005 box, I can just revert back to the 2000 setup by re-attaching the second copy of the non-corrupt MDF, right?The key question: is detach from sql 2000, attach to 2005 supported by Microsoft?thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-20 : 14:39:24
|
| Yes , it is supported 100% . But to be on safe side, You can backup database in 2000 incase you can't attach to SQL 2005. You can't revert back to SQL 2000 after you tried to attach in 2005 and didn't work. |
|
|
|
|
|
|
|
|
From 2000 to 2005: detach, attach works well?