September 25, 2007 at 9:38 am
[font=Verdana]Hi,
I restored SQL 2000 Database to SQL 2005 and wanna "Repopulate all Full Text catalogs".
Please let me know how to do this.[/font]
October 23, 2007 at 11:40 am
The document is excellent!!!
I need something like that, but i need to do a Side by Side Migration.
Could you give some tips, best-practices,...
Thank you;)
January 29, 2008 at 9:13 am
Hi,
Planning sql server 2000 upgrade to sql server 2005. I did a test side-by-side upgrade, installed the 2005 instance on the same box. Realized that the databases on the 2000 instance were automatically replicated to sql 2005. I did backup on sql 2000 and restored to sql 2005. Realized that when i dropped the DBs in the 2000 instance the same DBs in the 2005 instance were also dropped.
Please help
January 29, 2008 at 11:42 pm
In side by side upgrade, sql server 2000 database never gets upgraded automatically. SQL 2000 Database need to be manually ported to the new verstion.
I think, you viewed the old instance of SQL server 2000 using SQL server Management studio and thought it got upgraded. And then from Enterprise manager you droped the same database :(.
I hope you have the backup. Restore the backup in the new instance you created and then change then compatibility level as mentioned in the document.
Let me know incase of any further query.
Regards
Sachin
January 30, 2008 at 12:46 am
Thanks Sachin,
I realy apprecaiate your timely response. I did a restore to ANOTHER location, dropped the old DB in sql 2000, and the instance in 2005 was dropped asl well.
I will appreciate if you give me a step by step guide of how to do side-by-side upgrade using BACKUP of the DBs in the 2000 instance and RESTORE to the 2005 instance.
I have two issues raised by upgrade advisor. They are:
1. Outer join operators *= and =* are not supported in 90 compatibility mode
2. Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default
3. Column aliases in ORDER BY clause cannot be prefixed by table alias
I will appreciate a quick responce. I am already in the project and need to resolve these issues.
Regards,
Victor
January 30, 2008 at 5:47 am
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 :).
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
January 30, 2008 at 2:23 pm
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
February 6, 2008 at 1:14 am
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
February 6, 2008 at 2:35 pm
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
May 7, 2008 at 3:05 am
Good summary article, but is no substitute for MS's Technical Reference. If you are doing an upgrade be sure to read it.
July 25, 2008 at 5:49 am
🙂
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.:P
Thanks,
Syed Shahbaz Ali
August 11, 2008 at 10:48 pm
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
November 27, 2008 at 8:37 am
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.:)
December 4, 2008 at 4:35 pm
The upgrade advisor documentation answers this. Install UA on another server and run the analysis of your target server from there.
June 16, 2009 at 1:09 pm
Good article. Thanks for writing. Keep on writing.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply