Upgrade SQL 7 database with SQL 2005

Question

Post reply

Upgrade SQL 7 database with SQL 2005

mukesh3.k

Newbie

Points: 5
More actions
April 24, 2008 at 1:27 am

#123460

I want to upgrade SQL 7 database in SQL 2005.
We are Tables, using SP, User defined data type and some jobs.
In future I am not going to use any additional services in SQL 2005 liking reporting etc in current Database.
Pls advice concern area or problems we get? Or any one of you has similar experience pls share.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 1

- Check your sql7 service pac for the prereqs !

- Start with SQL2005 Upgrade advisor !

- run dbcc checkdb for all databases;

- if you can , don't upgrade, but migrate (i.e. make a fresh install of sql2005 , copy the users, grant the windows logins and then copy db per db.Don't forget the jobs !)

- pay special attention to DTS-packages.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rajankjohn SSCertifiable Points: 5061 More actions · Answer 2

and make sure that the statistics are updated and compatability level changes are taken care of...

http://rajanjohn.blogspot.com/

ML-DBA SSCommitted Points: 1765 More actions · Answer 3

ALZDBA (4/24/2008)
- Check your sql7 service pac for the prereqs !
- Start with SQL2005 Upgrade advisor !
- run dbcc checkdb for all databases;
- if you can , don't upgrade, but migrate (i.e. make a fresh install of sql2005 , copy the users, grant the windows logins and then copy db per db.Don't forget the jobs !)
- pay special attention to DTS-packages.

Why are you saying don't upgrade, but migrate

What are the reasons for this ? Any known problems ?

Thanks.

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 4

Indeed.

-If you can, migrate;

Reasons:

- in case of disaster you'll have to build your server exactly as your current server. i.e. start with install sql 7.0, restore your sql7.0 databases, then upgrade to sql2005 and restore your sql2005 databases !

The reason for this is security. When upgrading, SQLserver keeps the security settings,so everything will continue to work with then niew version/settings.

-If you start with a fresh sql2005 install, you'll have to open it up yourself (and document that ! )

Take the time to investigate the new security advises.