SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Upgrading a Database SQL 2000 to SQL 2005


Upgrading a Database SQL 2000 to SQL 2005

Author
Message
Sachin Samuel-286731
Sachin Samuel-286731
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 3646
Hi Victor,

Below are the steps

1) Install SQL Server 2005 as a different named instance. (If you have done it then you can ignore this step.)

2) Once installed, install the latest service pack (SP2) for sql server 2005.

3) You can also apply any hotfixes released after sp2. For the list you can check microsoft website.

4) Take a backup of database from the sql server 2000 instance and restore it in SQL SERVER 2005 INSTANCE. This is very important for u. I think here you are doing the mistake. I think you are restoring it in OLD instance of SQL server 2000 only and viewing it from SQL server management studio.

MAKE SURE You are connected to the new named instance of SQL server 2005 only.

6) Change the compatibilty level of the database restored in the sql server 2005. (you can do it from SQL server management studio or can also execute the command mentioned in the article.)

And you are done Smile.

Make sure to create any dependent job or linked server if requried.

Below are my comments for the issues.

1. Outer join operators *= and =* are not supported in 90 compatibility mode


In SQL Server 2005, old method of writing Left outer join or Right outer join are not supported any more. You have to replace them with "Left outer join" and "Right Outer join" respectively.


2. Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default.


Incase you have full text index in your database then drop them before upgradin into SQL server 2005, else your upgradation can fail.

Please read the article. I have mentioned the script to delete all full text indexes.

Once the database is upgraded recreate the FT indices which were removed. So make sure to note tables and columns which have full indices before deleting them so that you can create them later.


3. Column aliases in ORDER BY clause cannot be prefixed by table alias


If you have any query like,

Select studentid, count(studentid) as the CountofStudents from students group by studentid order by CountofStudents

Doing an order by on alias created "CountofStudents" can work in SQL server 2000 but in case of SQL server 2005, it won't. So you have to mention the exact column name i.e "count(studentid)" and not the alias name.

Hope that helps.

Regards
Sachin
victsun
victsun
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 18
Thank you so much. You are very kind! Two more issues:

1. I am installing the SQL 2005 instance on the same physical box containing SQL 2000 instance. Are there any special precautions i need to take?

2. How do i decomission the SQL 2000 instance on the same box without affecting the 2005 instance?

Thanks once again for your time. Expecting your reply,

Victor
sachinsamuel1
sachinsamuel1
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 3626
Hi Victor,

Please find my reply below.


1. I am installing the SQL 2005 instance on the same physical box containing SQL 2000 instance. Are there any special precautions i need to take?


Installation of another sql server instance is completly independent activity.
Still you can check for the below.

a) Make sure you have enough space on the drive where you are isntalling sql server 2005.
b) Make sure to choose a different instance name other than SQL server 2000.



2. How do i decomission the SQL 2000 instance on the same box without affecting the 2005 instance?



Again, as SQL server 2005 is a differnet setup, it will not affect sql server 2000 instance. Once sql server 2005 instance is installed, shutdown sql server 2000 services and see if you are still able to access you new sql server 2005 instance and database. I think that will be safer than removing the database

Regards
Sachin



victsun
victsun
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 18
Thank you very much. I succesfully upgraded one of our DBs to 2005 today in a test environment. I will watch it for some days before going LIVE! Thanks for your help.

Victor
Preet_S
Preet_S
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 240
Good summary article, but is no substitute for MS's Technical Reference. If you are doing an upgrade be sure to read it.
shahbaz.oradba
shahbaz.oradba
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 102
Smile
EXCELLENT article Sachin,keep it up.
u have just simplified a complicated task ,before reading this i was totally unaware of inplace and side by side upgrade techniques,now it is clear to me
it helped a lot in understanding and seems to be a simple job.Tongue




Thanks,
Syed Shahbaz Ali
kiran_84
kiran_84
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 7
Hi Sachin,

I am in mids of upgrading from SQL server 2000 to 2005.

Wanted to get some advise from you guys, on what kind of way shall i use.
Let me inform you my current info.

- Having 2 servers to Upgrade from 2000 to 2005, 1 production server, 1 reporting server
- Both running on SQL 2000 now perfectly.
- just need to upgrade from SQL 2000 to 2005.
- Not a very complex and Large DB

What do you advise me to do?

Copy DB wizard? or Backup and Restore? or others
How to do, step-by-step would be great.

I would really appriciate all the help and support you can give me.
Sharing knowledge is one of the best deeds a person can do, and looking at your previous replies. you helped Victor alot. You are a good person.
Maybe 1 day i can share this knowledge with others.
Thanks Sachin,

Kiran
extremenovice
extremenovice
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 1140
Hi Everyone,

this thread and the journal for upgrading has been extremely helpful in my quest as a very new sql dba and researching on how to upgrade from sql 7 to sql 2005.

Can you tell me when running the Upgrade Advisor what kind of extra load does it place on the server its running on? Also can it be done while all the databases are online?

Thx in advance.Smile
Preet_S
Preet_S
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 240
The upgrade advisor documentation answers this. Install UA on another server and run the analysis of your target server from there.
Mark Thabet-457565
Mark Thabet-457565
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 83
Good article. Thanks for writing. Keep on writing.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search