September 14, 2004 at 9:19 am
I have been migrating Databases from a SQL Server 7.0 Instance to a 2000 Instance. Basically the method I use is as follows:
a)Create a new Database on the Destination Server (same name as the Source) When I create the new Database on my destination server, the compatibility mode is '80' and the source is always '70'
b) do a 'revlogin' on the Source Server and use the output from that query to recreate the logins on the destination server
c) make sure that the default DB for the newly creates logins are correct and change them if necessary
d) Backup the DB on the Source Server and Restore it to the Destination Server.
e) Check login permissions and fix any orphaned users - usually I don't find any that need to be fixed, but I always check.
I've read BOL on Changing the compatibility mode on the Database... but I'm still unsure when I would have to do this and why???????????????? SHOULD I be changing the compatiblity mode from 80 to 70 when migrating Databases from 7.0 to 2000???? Any advice on moving DBs in this manner would be appreciated.
September 15, 2004 at 2:17 am
Usually, when I migrate from 7 to 2000, I have found the easiest way is to detach the database from v7 using sp_detach_db, copy the MDF (and the log if necessary) to the destination server, and use sp_attach_db to re-attach it.
you will still need to clear out orphaned users, and such.
-- Alex
September 15, 2004 at 4:54 am
Thanks for the reply!! The reason I use Backup and restore is because a lot of the time I am refreshing from production (7.0 instance) to a pre-production 2000 instance for tesing purposes. If I was to detach - I'd have to do it off-hours which is nearly impossible. I still don't understand when - if ever - the compatibility mode would need to be changed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply