SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Detach SQL Server 7 DB and attach to SQL Server 2000


Detach SQL Server 7 DB and attach to SQL Server 2000

Author
Message
Bernard Omiple
Bernard Omiple
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 1

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


Martin Cairney
Martin Cairney
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 161

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
Yelena Varshal
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4678 Visits: 596

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 Varshal

mimorr
mimorr
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

Group: General Forum Members
Points: 502 Visits: 52
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search