Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Detach SQL Server 7 DB and attach to SQL Server 2000 Expand / Collapse
Author
Message
Posted Thursday, November 18, 2004 10:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 2, 2006 9:58 AM
Points: 56, 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

Post #147112
Posted Thursday, November 18, 2004 11:55 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 27, 2014 9:30 PM
Points: 509, 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.

Post #147117
Posted Friday, November 19, 2004 8:34 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 AM
Points: 3,475, Visits: 584

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

Post #147220
Posted Friday, November 19, 2004 8:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:13 AM
Points: 380, 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
Post #147228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse