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 |
|
itsonlyme4
Posting Yak Master
109 Posts |
 Posted - 2009-09-02 : 14:11:28
|
| We have A SQL Server 2000 Server with Several Instances and a seperate SQL Server 2005 Server built out with the same instances and I have been migrated databases from 2000 to 2005 by scripting out the logins on the 2000 server and recreating them on the 2005 server and then doing a backup and restore (2000 to 2005) So far this has worked great. is there ever a need to actually do an upgrade as opposed to restoring a 2000 database to 2005? I thought that a migration of sorts took place when you did the restore? can anyone explain? |
|
|
scuzymoto
Starting Member
17 Posts |
Posted - 2009-09-02 : 14:22:39
|
| It depends on the method your using to 'migrate' or 'restore' the data. Detaching and attaching for example will keep the compatability level of the original database the same. So you may want to go in after your all done and manually switch the compatability level up to '90'. Google 'sql compatability level' for lot's of good info.If you created the databases from scripts or by hand on the 2005 server and are only copying/restoring the data, then compatability level should already be at the 2005 level on your new instance. A simple right click on the database and it's 'options' will tell you the level. Switching this level is a very simple process but you will want to research a little to make sure it doesn't affect your apps in a negative way. Going back to a previous compatability level is possible if you situation requires but not if you started taking advantage of new features only available in 2005. |
 |
|
|
ScottWhigham
Starting Member
49 Posts |
Posted - 2009-09-03 : 07:27:21
|
quote:
Originally posted by itsonlyme4is there ever a need to actually do an upgrade as opposed to restoring a 2000 database to 2005? I thought that a migration of sorts took place when you did the restore? can anyone explain?
Sure but it's not as common as a migration. In an upgrade, you are using the same hardware and, in most cases, you have a shiny new machine that your SQL Server 2005 is installed on (thus it becomes a migration). And you are correct in that the database is "upgraded" during a restore database.========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-09-03 : 07:41:55
|
quote:
Originally posted by ScottWhighamAnd you are correct in that the database is "upgraded" during a restore database.========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx
Not quite. It is put in 2000 compatibility mode if there are potential issues, which will affect performance. |
|
|
|
ScottWhigham
Starting Member
49 Posts |
Posted - 2009-09-03 : 13:33:41
|
quote:
Originally posted by RickD
Not quite. It is put in 2000 compatibility mode if there are potential issues, which will affect performance.
Technically, I think I am correct here, Rick. The database is upgraded when you take a previous-version backup and restore it onto a new version. Here's how you can verify such: Go take a backup of a SQL 2000 database and restore it onto a SQL 2005/2008 server. Now take a backup of this newly restored database and try to restore it onto the SQL 2000 server - you can't because the database page and internal structures have all been upgraded. Compatibility mode is a separate topic - it defines how the SQL engine responds to statements; not how the pages are created, which system objects there are, etc. Compatibility mode is important to know when discussing upgrades and migrations but it has no effect on whether the database was "upgraded" during a restore. I'd suggest anyone interested in learning more check out BOL for how to do this.========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx |
|
|
|
|
|
|
|
|
sql server migration (2000 to 2005) question