SQLServerCentral / Discuss Content posted by Sachin Samuel / Article Discussions / Article Discussions by Author / Upgrading a Database SQL 2000 to SQL 2005 / Latest Posts

RE: Upgrading a Database SQL 2000 to SQL 2005

sejalp — Wed, 03 Nov 2010 13:21:00 GMT

Hi, how to do side-by-side upgrade from sql server 2000 to sql server 2008 R2.Asking for username and password while doing it, which username and password should be used?

RE: Upgrading a Database SQL 2000 to SQL 2005

kjplreddy — Mon, 13 Sep 2010 22:26:00 GMT

hi,can u plz give me the script for deleting full text indexes while upgrading 2000 to 2005Regards,Jayapal

RE: Upgrading a Database SQL 2000 to SQL 2005

bharadwaj.manchu — Wed, 21 Jul 2010 15:28:35 GMT

Yes, I agree with sachin

RE: Upgrading a Database SQL 2000 to SQL 2005

SQL Stud — Mon, 25 Jan 2010 08:41:21 GMT

http://msdn.microsoft.com/en-us/library/ms188414.aspxOne thing to keep in mind, after upgrade from SQL 2000 to SQL 2005 or SQL 2008, it is best to run the following:DBCC UPDATEUSAGE (0);GOthis will prevent certain system functions and procedures (i.e. sys.spaceused or sysindexes) from returning an error. example: A user runs [b]select * from sysindexes [/b](which I know is legacy, but some of us still reference these as we are too lazy to update our code). The following error is returned...[i]Attempting to reference database fragment 1 in database 'foo' which does not exist. Contact Technical Support.[/i]Now run the following against the database:1. dbcc checkdb to make sure you don't have a consistancy issue2. If dbcc checkdb returns an error indicating an allocation unit ID error, run dbcc updateusage(0)You may also find you get this same error on a database snapshot. I haven't found that there is anything you can really do since the DB Snapshot is read only. your best bet is to simply drop the database snapshot when you are done with it.

RE: Upgrading a Database SQL 2000 to SQL 2005

Stan Pluzak — Thu, 08 Oct 2009 07:59:26 GMT

Hi SQL,We'll be upgrading from 2000 to SQL 2005 (in place) in a couple weeks and I have two questions. 1) We have ~240 db's totalling 530 GB of data, Any idea what kind of timeline we'll be looking at once the upgrade begins? Is there a rough GB/Min upgrade rate?2) Second question, I figure updating the statistics after the upgrade completes could be quite lenghty, again any idea on whether we could be looking at 1 hour 10 hours?Thanks,Sp

RE: Upgrading a Database SQL 2000 to SQL 2005

samin-538719 — Wed, 26 Aug 2009 17:49:29 GMT

How do I determine if I have full-text search on SQL 2000 instance? I am getting this message with the upgrade advisor "Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default."

RE: Upgrading a Database SQL 2000 to SQL 2005

Mark Thabet-457565 — Tue, 16 Jun 2009 13:09:25 GMT

Good article. Thanks for writing. Keep on writing.

RE: Upgrading a Database SQL 2000 to SQL 2005

Preet_S — Thu, 04 Dec 2008 16:35:16 GMT

The upgrade advisor documentation answers this. Install UA on another server and run the analysis of your target server from there.

RE: Upgrading a Database SQL 2000 to SQL 2005

extremenovice — Thu, 27 Nov 2008 08:37:23 GMT

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.:)

RE: Upgrading a Database SQL 2000 to SQL 2005

kiran_84 — Mon, 11 Aug 2008 22:48:24 GMT

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 DBWhat do you advise me to do?Copy DB wizard? or Backup and Restore? or othersHow 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

RE: Upgrading a Database SQL 2000 to SQL 2005

shahbaz.oradba — Fri, 25 Jul 2008 05:49:31 GMT

:)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.:PThanks,Syed Shahbaz Ali

RE: Upgrading a Database SQL 2000 to SQL 2005

Preet_S — Wed, 07 May 2008 03:05:43 GMT

Good summary article, but is no substitute for MS's Technical Reference. If you are doing an upgrade be sure to read it.

RE: Upgrading a Database SQL 2000 to SQL 2005

victsun — Wed, 06 Feb 2008 14:35:26 GMT

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

RE: Upgrading a Database SQL 2000 to SQL 2005

sachinsamuel1 — Wed, 06 Feb 2008 01:14:34 GMT

Hi Victor,Please find my reply below.[quote]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?[/quote]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.[quote]2. How do i decomission the SQL 2000 instance on the same box without affecting the 2005 instance?[/quote]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 databaseRegardsSachin

RE: Upgrading a Database SQL 2000 to SQL 2005

victsun — Wed, 30 Jan 2008 14:23:49 GMT

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

RE: Upgrading a Database SQL 2000 to SQL 2005

Sachin Samuel-286731 — Wed, 30 Jan 2008 05:47:01 GMT

Hi Victor,Below are the steps1) 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.[quote]1. Outer join operators *= and =* are not supported in 90 compatibility mode[/quote]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.[quote]2. Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default.[/quote]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.[quote]3. Column aliases in ORDER BY clause cannot be prefixed by table alias[/quote]If you have any query like,Select studentid, count(studentid) as the CountofStudents from students group by studentid order by CountofStudentsDoing 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.RegardsSachin

RE: Upgrading a Database SQL 2000 to SQL 2005

victsun — Wed, 30 Jan 2008 00:46:52 GMT

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 mode2. Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default3. Column aliases in ORDER BY clause cannot be prefixed by table aliasI will appreciate a quick responce. I am already in the project and need to resolve these issues. Regards,Victor

RE: Upgrading a Database SQL 2000 to SQL 2005

sachinsamuel1 — Tue, 29 Jan 2008 23:42:08 GMT

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.RegardsSachin

RE: Upgrading a Database SQL 2000 to SQL 2005

victsun — Tue, 29 Jan 2008 09:13:08 GMT

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

RE: Upgrading a Database SQL 2000 to SQL 2005

jrodolfoa — Tue, 23 Oct 2007 11:40:17 GMT

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;)

RE: Upgrading a Database SQL 2000 to SQL 2005

kprabhakar1978 — Tue, 25 Sep 2007 09:38:26 GMT

[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]

RE: Upgrading a Database SQL 2000 to SQL 2005

Dinesh-455184 — Sun, 01 Jul 2007 01:22:00 GMT

Article is well structured, concise and to the point. It reflects a competency level of an author in this topic. I find it interesting and easy to digest inspite of a fact that the database upgradation has never been my cup of tea.I really wonder what would happens to a pre-compiled stored procedures which have been created by a user? Would there be any impact on those stored procedures ?Keep writing more stuff (:-RegardsDinesh

RE: Upgrading a Database SQL 2000 to SQL 2005

Carlos Sepulveda — Fri, 01 Jun 2007 10:02:00 GMT

Besides Sachin's guide, I'm also reviewing book "Microsoft SQL Server 2005 Unleashed" (SAMS pub) for the proper upgrade path, and find the information about Fulltext indexes conflicting.

While Sachin states Fulltext indexes should be completely removed before upgrading and recreated, the Unleashed text states these are taken care of in both Side-by-Side and In-Place upgrades:

From page 208 for Side-by_Side: "You might be wondering how the [Copy Database] wizard handles your full-text catalogs. Rest assured, they are imported by default during either a restore, an attach, or via the Copy Database Wizard..."

From page 217 for In-Place: "...When your upgrade of the database engine is complete, it is recommended that you perform the following on all databases (also recommended for side-by-side migration): -Repopulate your full-text catalogs. (This is automatically done for databases where Full-Text indexing was enabled prior to the upgrade.)..."

I did not find any reference in the Unleashed text about removing the full-text indexes prior upgrading at all.

What gives???

Thanks for your replies. Great article by the way.

Charlie S.

RE: Upgrading a Database SQL 2000 to SQL 2005

Lee Davis-285380 — Fri, 18 May 2007 10:04:00 GMT

The article is complete and it asissted me in doing the upgrade. I've asked my fellow consultants in my company to consider this article as the best practices for our team.

Thanks for the hardwork that was necessary to create this article!!

RE: Upgrading a Database SQL 2000 to SQL 2005

mamta patni — Fri, 18 May 2007 02:28:00 GMT

Nicely written article, gives a fair idea about SQL database upgrade.

keep it up !!

Mamta

RE: Upgrading a Database SQL 2000 to SQL 2005

SRDEV — Wed, 16 May 2007 06:26:00 GMT

This article certainly highlights the difficulty of performing quality technology services when basic communications skills are substandard. This article is a difficult read, not for the technical content, but for the grammatical errors, poor sentence structure and even spelling mistakes. While the author's technical skills may be adequate, writing such an article to build a reputation, gain clients and impress others may have a very different effect. Such an article should demonstrate a certain skill level in two areas, technology and communications. This author has one. We should, and this board should, strive for excellence in both.

RE: Upgrading a Database SQL 2000 to SQL 2005

Mark Lar — Wed, 16 May 2007 05:36:00 GMT

Standard Edition + latest service pack.

Thanks

RE: Upgrading a Database SQL 2000 to SQL 2005

Sachin Samuel-286731 — Wed, 16 May 2007 05:28:00 GMT

Mark,

Which Edition of SQL server 2005, you have upgraded to?

Regards

Sachin

RE: Upgrading a Database SQL 2000 to SQL 2005

Mark Lar — Wed, 16 May 2007 05:13:00 GMT

Sachin

No, I can only see my BackupExec service and not my SQL2005 service via option 1!!

Any ideas?

Thanks.

Mark

RE: Upgrading a Database SQL 2000 to SQL 2005

Sachin Samuel-286731 — Wed, 16 May 2007 04:42:00 GMT

Mark,

On clicking Surface area configuration link, a small window appears with the following links

1) Surface Area configuration for Service and connections.

2) Surface Area configuration for features.

Seems like you clicking only on second link to check for the services.

You can manage Sql Server 2005 services as well as remote connections from the first link.

Please let me know incase you have any query or need further clarifications.

Best Regards

Sachin

RE: Upgrading a Database SQL 2000 to SQL 2005

Mark Lar — Wed, 16 May 2007 02:12:00 GMT

Thanks Sachin

Sorry maybe I did not make myself quite clear. The surface area configurator does not find my sqlserver service, so I am unable to configure the service particularly email notifications.

How can I get it to find my sqlserver service?

Thanks

RE: Upgrading a Database SQL 2000 to SQL 2005

Sachin Samuel-286731 — Wed, 16 May 2007 01:51:00 GMT

Hi Mark,

Surface area never displays your database. But the services installed on your box. It provides interface for enabling or disabling many Database Engine, Analysis Services, and Reporting Services features. Disabling unused features helps to secure your Microsoft SQL Server installations by reducing the SQL Server surface area.

Hope I answered your query.

Regards

Sachin

RE: Upgrading a Database SQL 2000 to SQL 2005

Mark Lar — Wed, 16 May 2007 01:39:00 GMT

We carried out an inplace upgrade and the database works fine, BUT, when we run the Surface Area Configurator the database is not found! Any ideas please?

Thanks

RE: Upgrading a Database SQL 2000 to SQL 2005

Deepa V — Tue, 08 May 2007 07:04:00 GMT

Excellent Article! Step by step details with snapshot that makes it easy for the first time user. Including all the details.

Keep it up!

Deepa

RE: Upgrading a Database SQL 2000 to SQL 2005

Gaurav Kohli-415349 — Mon, 07 May 2007 06:01:00 GMT

Perfect article..... Simple and easy to understand even for someone like me with little or no idea about SQL

Good Going Sachin

Upgrading a Database SQL 2000 to SQL 2005

Sachin Samuel-286731 — Thu, 03 May 2007 11:45:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/ssamuel/2987.asp