Detach SQL Server 7 DB and attach to SQL Server 2000

Question

Detach SQL Server 7 DB and attach to SQL Server 2000

Bernard Omiple

SSC Veteran

Points: 278
More actions
November 18, 2004 at 10:34 pm

#85997

Hi all:
Are there any issues I need to be aware of when detaching SQL Server 7 databases and attaching them to a SQL Server 2000 server? The databases are not replicated.
Any information regarding this area will be very helpful.
Thanks in advance...
Bernard

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

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

Martin Cairney Right there with Babe Points: 793 More actions · Answer 1

Bernard

SQL Server 2000 will perform the necessary changes to the 7.0 database when you detach and attach. However, as it is a new version of SQL Server, there are some items to be aware of and prepare for:

check the default collation of your instances
check your logins and users - these may have to remapped afterwards
I assume that you have checked all your views, procedures etc for compatibility?
check your compatibility setting after attaching
do you have filegroups? Is your disk structure the same on both servers? Can you move your files if you have to?

If you have prepared well for your migration, it should go smoothly.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Hi Bernard,

1. Logins. Martin is right. The logins and the database owner will be things to watch. Is it the same Windows server (for example- 7.0 is a default instance and 2000 is named). Are those 2 different servers in the same domain? Are those servers in different domains? Who is a database owner? SA, standard login, local Windows login, Domain login? The same question about users. Will the account with the same SID exist on another server? If you have to re-map users: Do they own objects? Could they be dropped and re-created? How your app works with users? Does it use user names or user SIDs? What should be re-mapped to what: sysusers to syslogins or syslogis to sysusers. Do they have the same or different permissions? Are the logins being users in more then one database?

2. DRP (Disaster Recovery Plan) hopefully you are attachine a copy, not the current instance, so you can verify everything for #1 if you need after you attach.

3. Application stuff: you have to change all connections strings, ODBC sources, notify users, all this stuff.

4. Crossdatabase Ownership Chaining: SP3 for SQL Server 2000 has this checkbox. Do you work accross databases?

5. I did see one app that did not work after a minor SQL Server security patch, not talking about version upgrade.

6. Did you modify system stored procedures? Could be some surprises. Email me if you have issues with that.

Everything above is applicable to both types of upgrade: attaching and backupg up and restoring, ##1,2 and 3 are also applicable to just moving a database

Yelena

Regards,Yelena Varsha

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 3

Make sure you have some extra space in your primary filegroup and in the log file before detaching from SQL7.0. The upgrade requires some additional space.

Michelle