Upgrading to SQL 2005

Question

Post reply

Upgrading to SQL 2005

PJ-200411

SSC Enthusiast

Points: 156
More actions
November 17, 2005 at 3:15 am

#166060

Hi All
I have a number of SQL 7 and SQL 2000 databases, we are looking into upgrading to SQL 2005. Is it possible to upgrade directly from SQL 7 to SQL 2005 or should the database be upgraded first to SQL 2000 and then onto SQL 2005.
Are there any potential problems to watch out for?
Is there achecklist floating about?
Any help would be much appreciated
Many Thanks

PJ

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Jakob SSCommitted Points: 1720 More actions · Answer 1

My suggestion is simple:

Try the upgrade in a test environment first, and see what happens.

There is a SQL 2005 upgrade wizard that runs, and will produce a nice report of any compatibility issues and/or upgrade concerns. You could print this out, and review.

Good luck.

- john

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 2

You can upgrade directly from SQL 7.0 SP 4 and SQL 2000, there is no need for an intermediary step.

PJ-200411 SSC Enthusiast Points: 156 More actions · Answer 3

Thanks for your replies, I tried to use the copy databse wizard and got the following error message

TITLE: Copy Database Wizard

------------------------------

Failed to connect to server mydatabase.

------------------------------

ADDITIONAL INFORMATION:

This SQL Server version (7.0) is not supported. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

BUTTONS:

OK

------------------------------

The database I am trying to upgrade is running on SQL server 7.0 SP 4

select @@version gives

The version of SQL 2005 I have installed is below

Should I be using another method to convert that database?

Many Thanks

PJ

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 4

Ah, that's not upgrading! That's migrating!

You can upgrade your SQL 7 Server to SQL 2005, by running the setup and choosing a default installation. It will detect the existing SQL 7 and upgrade it.

You *must* have a full backup of the server before you do this.

PJ-200411 SSC Enthusiast Points: 156 More actions · Answer 5

It all starts to make sense!

Is it possible to migrate a SQL 7 db to SQl 2005 or do I have to first migrate to SQl 2000 then to SQL 2005.

The reason why I don upgrade the installation is that the server is not upto spec and that I would liket to havee both installations running incase of an problems arising so that I have to reverto the SQL 7 db

Many Thanks

PJ

barsuk SSChampion Points: 12280 More actions · Answer 6

In my test environment I was able to restore SQL Server 2000 Database (2 gb) on SQL Server 2005...

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 7

There is no change in the physical file structure of database files between SQL Server 2000 and SQL Server 2005, hence you are able to restore 2000 databases to 2005.

The structure for SQL 7.0 database files is different.

PJ-200411 SSC Enthusiast Points: 156 More actions · Answer 8

So I guess I should start migrating the databases to SQL 2000 so I can then migrate to SQL 2005

Thanks for your help.

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 9

It depends on your preferred plan.

(Assuming a current server on SQL 7 SP4)

Option 1 (Upgrade in place):

Take a full server backup of your existing system.

Install SQL 2005 (overwrinting exisint ginstallation and upgrading databases)

Testing

Then UAT or restore from backup

Option 2 (Migrate to new server)

Backup

Upgrade to SQL 2000

Test

UAT/Restore

Install new server/SQL 2005

Migrate databases

Test

UAT/Fallback to old server

PJ-200411 SSC Enthusiast Points: 156 More actions · Answer 10

PJ-200411

SSC Enthusiast

Points: 156

November 21, 2005 at 8:15 am

#604978

Thanks for your help