| Author |
Topic |
|
mcmcom
Starting Member
9 Posts |
 Posted - 2008-03-26 : 12:16:04
|
| hi all, we are upgrading one of our db servers to SQL 2005 from 2000 in a couple months. My development DB server is still SQL 2000. will it be possible to still create my databases on Sql 2000 (in dev) first and then transfer them to the 2005 server for production? Or should i install sql server 2005 on the development machine as well? Also im starting an upgrade roadmap. are there any special things i should watch out for when converting databases from sql 2000 to sql 2005 ? thanks,mcm |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-26 : 12:25:17
|
| Install SQL server 2005 and use backup/restore to move database from 2000 to 2005. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 13:31:44
|
| You can do that but it would mean that you won't be using any new features and also might come across differences between the two editions that could cause problems in production.I wouldn'y consider doing it - too risky.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-26 : 14:42:22
|
| Well, after you restored to 2005 you can change compatibility to 90 to use new features but there are some features that won't work in 90 compatibility. Make sure you check that, otherwise i would say this is the best option. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 14:48:15
|
quote:
Originally posted by sodeep
Well, after you restored to 2005 you can change compatibility to 90 to use new features but there are some features that won't work in 90 compatibility. Make sure you check that, otherwise i would say this is the best option.
Except that you wouldn't be able to implement anything using new features as the dev system is v2000.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-26 : 14:56:11
|
| Yes you can if you restore to 2005 and change compatibility to 90. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 15:00:12
|
quote:
Originally posted by sodeep
Yes you can if you restore to 2005 and change compatibility to 90.
Ok - you have a v2000 dev system. How do you develop features on that system that are only available on v2005.Ignore the restore or compatability level we are working on a v2000 server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mcmcom
Starting Member
9 Posts |
Posted - 2008-03-26 : 15:21:23
|
| thanks for all the tips guys. One more question. I have 2005 and 2000 running on the same server. Right now i have several private ips that map to this server. Example:192.168.8.8 can be used to connect to SQL Server 2000 i added 192.168.8.9 in hopes i can use that to connect to sql server 2005. I set up network config to allow this address to go to SQL 2005 but when i try to connect with it i am connected to the Sql 2000 server. I cant seem to find anything in SQL Server 2000 that will allow me to specify which ip addresses it should listen too. I can connect to 2K5 DB's if i use named pipes but is there a way to make 2000 only listen to one ip and 2005 listen to another? TIA,mcm |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 15:28:58
|
| Bit outside my experience but I think you might need to set it to listen on a different port then specify the port in the connection.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mcmcom
Starting Member
9 Posts |
Posted - 2008-03-26 : 15:34:47
|
| also regarding the upagrade roadmap. I have since installed a DEV version of SQL 2005 (i had the developer edition included with VS 2005 pro) so now i have 2000 and 2005 dev versions. Whats the best way to take a "copy" of a 2000 database and get it in and upgraded for 2005? tia,mcm |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-26 : 22:38:44
|
| Backup db from sql2k then restore it on sql2k5. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 22:54:24
|
| You will also need to set the compatability level afeter the restore.Note that as you aren't upgrading the production servers for a couple of months you will still need to develop using the v2000 version.You can use your dev instances to develop and test parts of the live upgrade process.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mcmcom
Starting Member
9 Posts |
Posted - 2008-03-27 : 10:22:41
|
| So Backup and restore. will that take the transaction logs too?And what exactly do i have to do for compatibility, just properties of the database and change mode to 90? thanks,mcm |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-27 : 10:38:42
|
| >> So Backup and restore. will that take the transaction logs too?Yes they are needed for the restore.>> And what exactly do i have to do for compatibility, just properties of the database and change mode to 90? If you want to use v2005 features.You will need to test the system first to see if there are any problems - you will need to that even if you don't set it to 90.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Sql Server 2000 and 2005