Complete DB migration in SQL7 1

[Averno]

Hi, this is my situation: we've just completed the TEST development of our datamart databases on a Compaq 8000. Now is time to migrate ALL the "datamart" (3 DBs) with ALL of the objects (db, sp, diagrams, roles, rules, ecc..) to the PRODUCTION hw (compaq 8500, SQL7). My question is: is it better to backup and restore or manage DTS? Which of these 2 ways assures a COMPLETE migration? Is there something else i don't know i'd have to consider?
Thanks a lot, bye.
Averno, Italy.

 

[tlbroadbent]

My recommendations, in order of my preference is:
[ol][li]Detach the databases on the development server. Copy yhe datbase files to the new server. Attach databases in new server. (See sp_detach_db and sp_attach_db in SQL BOL.)
[li]Backup databases on 1st server and restore on 2nd server.
[li]Use DTS to transfer databases.[ol]I prefer method one because it is fast a relatively error free. The only difficulty you'll encounter is that database users don't map correctly to logins. This is easily corrected by following the instructions given by Neil Boyle on SWYNK.com. See his article "Fixing broken logins and transferring passwords" at

http://www.swynk.com/friends/boyle/fixingbrokenlogins.asp.

Transferring databases with backup and restore will also result in broken logins. You'll need to follow the Boyle procedure to correct the user/login connection.

DTS is very powerful but I've found it to be slower and more error prone. Sometimes two or more passes are required to completely transfer databases. It may just be my laziness and preference for the other mehods that prevent me from fully understanding and utilizing DTS. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot