Upgrade SQL Server 2000 to 2005

[MSR63]

Hello all,

I am a newbie to SQL and inherited SQL2000 a few months back. The HDs in the server were degraded and crashed before a good backup was performed.

I replaced the HDs and restored from the latest BU I could, I then found out about Data Maintance Plans - which gave me a more update backup.

Since then - I have struggled with SQL and creating new maintenance plans (the full securities didn't copy over from backup). Logs are too big, the order I have them is not correct.

I am drained from SQL2000 - I just took a 35 hour course only to learn what I already learned in panic mode when the server crashed. Now I have been told by my executives to upgrade from 2000 to 2005 - we are keeping our existing 32bit server.

Any good - "great detailed links" to help me with this upgrade would be greatly appreciated. I am a one man team and I don't have time for servers/software to break down

Any suggestions are greatly appreciated

 

[Skittle]

The two options you have are to

1) Upgrade existing server to 2005 by overwiting
SQL 2000 DLL's

2) Install a new instance of SQL 2005 and copy the databases
from 200 to 2005.

You can do this most easiest with the copy wizard.
Right click the database and select tasks->copy database.
I think it copies logins and schedules as well.

You can also detach databases and attach then to
2005 but they are not backward compatible so
backup!!!
This method definately does not include loginas and
scheduled jobs unless you include master and msdb.

You can of course backup 2000 databases with the
BACKUP and restore each database to 2005.
Again this method soes not include schedules jobs
and logins unless you include the master and msdb.

What ever you do, backup everything every step of the way.
Set up a plan first and test it.

I have to move databases from SQL 200 to 2005 soon.
I plan to install a seperate instance on a seperate machine
and copy using the wizard as I believe it also copies schedules jobs, logins etc and requires least preperation.
I will still be testing it first though.



Dazed and confused

 

[wbodger]

Hmm... I am in a similar boat (well, I need to upgrade anyway and I have a new server). Does the copy database wizard work from 2000 to 2005? And does it, in fact, carry over EVERYTHING in the database, including logins?

Willie

 

[MSR63]

Thanks -- I was also given this reply in another forum

Willie - Perhaps this will help you out also.

"a good starting point is to load up the sql 2005 upgrade adviser. This can point out problems that can occur so you can fix them in advance to your upgrade "

 

[Skittle]

The upgrade advisor is the first step of overwriting your existing 2000 programs with 2005. If you make a full backup ofe evrything and if you can have 2000 unavailble until you have completed a successful upgrade then you can go this route.

My preference would still be to copy so that if something goes wrong, I still have a functioning server.

The copy wizard ( according to my MS 70-431 book ) can

- Select source and destination servers
- Select 1 or more databases to be moved and upgraded
- Specify the file locations
- Create logins on the destination server
- copy supporting environment objects such as jobs, error messages, user defined procedures and objects
- define a schedule for performing the copy

OK so it's not specific about login copy.
Its something I will test before I eventually do the upgrade.


Dazed and confused

 

[SQLWilts]

Try googling sp_help_revlogin and sp_help_revlogin_2005 to move your logins... passwords and all