Restore SQL7 to SQL 2005

Question

Restore SQL7 to SQL 2005

the-devastator

Valued Member

Points: 61
More actions
April 11, 2007 at 3:43 am

#172970

Hi,
ive set-up a new SBS 2003 Server with SQL 2005 server .. my old NT4.X server with SQL 7 is still in use .. so ive setup a duplicate server so i can upgrade the SQL 7 server..
anyone got a manual ?

Viewing 7 posts - 1 through 6 (of 6 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Since you post is caled restore sql7 to 2005, the answer is yes you can restore SQL7 backups on a 2005 server. You might want to change the compatibility level to 9.0 after the restore.

If your looking for a genral upgrade "manual" have a look here: http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en

Markus

[font="Verdana"]Markus Bohse[/font]

Coll77 SSC Veteran Points: 282 More actions · Answer 2

Markus, I was just going to post this very question.... so to confirm, if I restore a SQL 7 database onto a SQL 2005 server and change it's compatibility level it will effectively become a 2005 database?

Cool! then all I need to do is rebuild my indexes and test to shake out any strange niggles

Coll

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

James,

yes that's the right approach. I forget to mention in my earlier post that you shoould run update statistics after restoring the databases to SQL 2005, because the old statistics become invalid.

Markus

[font="Verdana"]Markus Bohse[/font]

Coll77 SSC Veteran Points: 282 More actions · Answer 4

Thanks Markus that should save me some time this weekend

AnilKumar-428256 SSC-Addicted Points: 404 More actions · Answer 5

You can use below command to change the compatibility to 90.

Exec sp_dbcmptlevel Db_Name, 90

Anil Kumar

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 6

Hold your horses !

It can be done, but will your applications still work ?

Use the SQLUA (Sqlserver Upgrade Advisor) to check your current db-stuff.

e.g. queries using the outerjoinsyntax *= or =* may not funtion the same way or may not function at all !!

I think bit-columns in sql7 are stored 0 or -1 whilst in sql2000 and 2005 they are 0 and 1

Check your applications for this behaviour.

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