upgrading sql2000 to sql2005 (same machine)

Last Post 06 Oct 2009 05:06 PM by ctseah. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
ctseah
New Member
New Member

--
28 Aug 2009 12:34 AM

Hi,

Would like to seek your opinion on upgrading sql2000 to sql2005 in the same machine.

I intend to install sql2005 default or name instance into current server A.

Server A has also got sql2000 default instance with 10 user databases.

The steps which I think I will carry are these.
1) can I install sql2005, do I need to give an instance name ?
2) apply the necessary service pack for sql2005.
3a) detach the 10 user databases and attached them to sql2005.
3b) or do a backup of these 10 user databases and restore them to sql2005.
4) shutdown the sql2000 instance and leave the sql2005 running.
5) if there is any problem or issue with this sql2005, I will shutdown sql2005 and bring up sql2000 as a contingency plan.

But I have this doubt is that how will the application connect to the sql2005 instance ? How does it know which instance to connect to ?? Is it from the ODBC setup ?

Given the above method, do I need to pay special attention to the sql2005 instance name ?

Thanks &
Regards
ctseah
New Member
New Member

--
01 Sep 2009 06:51 AM
Hi,

I created same database name in sql 2005 and do a backup of the sql 2000 database, then tried to restore it to the database in sql 2005.

The restore failed with error "...does not belong to the database..." Come to think of it, the reason could be it did not expect to receive a backup file that does not belong to sql 2005.

Would appreciate if you can confirm that backup and restore method for this upgrade will not work.

However I tried to deattach the database from sql 2000 and attach it to sql 2005, it worked !
Do I need to change the compatibility to sql2000 ? or can I leave it as sql 2005 ?

Hope to hear from you, thanks

Regards
ctseah
New Member
New Member

--
01 Sep 2009 08:25 PM
Hi,

After selecting overwrite option during the restore, it wored !.

Thanks rm.

Regards
ctseah
New Member
New Member

--
07 Sep 2009 06:16 PM

Hi,

How do I get the login to be brought over to the upgraded instance ?
In my case here I can manually create them as there are only few sql users which I can quite easily create and assign the roles and permission.

But if there a better way of doing it ?

Thanks &
Regards
ctseah
New Member
New Member

--
16 Sep 2009 07:08 PM
Hi,

Thanks Gunneyk and rm.
I managed to script the login, password and roles from using http://support.microsoft.com/?id=246133 .

I also realised that the application vendor had created tables in tempdb in sql2000.
These tables are needed for their application to run. I am puzzled why would application vendor want to create tables in tempdb and not in their customized database. The tempdb is usually not backup right ?

What is the best way to bring these tables about 10 of them including the records from sql2000 tempdb to sql2005 tempdb ?

Thanks &
Regards
ctseah
New Member
New Member

--
17 Sep 2009 05:04 PM
Hi,

That is a good point to note by rm and gunneyk on the fact that tempdb will be recreated at restarting of sql server. I will check with the application vendor on this.

Regards
ctseah
New Member
New Member

--
06 Oct 2009 05:06 PM
Hi,

Application vendor actually has a stored_procedure that creates these tables in the tempdb at every startup of the instance. So that settles it.

Another question is how do I know whether the current sql 2000 has reporting services installed ?
If the current sql 2000 has reporting services installed, then in my sql 2005 installation, I will also need to install reporting services. Is there such thing as reporting services in sql 2000 ? How to check whether it is there ?

Thanks &
Regards
taersious
New Member
New Member

--
12 Nov 2009 06:23 AM
Can you use a script to take a db backup from an instance of SQL 2000 and restore to an instance of SQL 2005 on the same server? If so, would this render the original (live) database on SQL 2000 unusable? I am looking for a way without detaching the live database to bring a copy online for test in SQL 2005.


Acceptable Use Policy
---