Re: Copying Databases From Server to Another

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 13:03:40 +0200

hi,
"Shash Goyal" <Shash703@gmail.com> ha scritto nel messaggio
news:uEjQ2LwtEHA.2808@TK2MSFTNGP14.phx.gbl
> What is the best and safest way to copy about 55 Databases from a
> server running win2k to a new server running Win Server 2003 Ent. Ed.
> What are the different options do i have? Can I copy all the current
> Db plans, jobs, and Logins? What is a good practice for doing this?
> The win2k server is in production right now. Do i have to shut it
> down?
>
> Please Help. Thanks

wow... a massive task, isn't it? =;-D

I'd suggest, if available, the Copy database wizard, available in Enterprise
Manager, for SQL Server instances, which takes care to move logins and msdb
objects too....

but... if you are on MSDE, you do not have access to it... an all becomes
trickier...
you can perhaps full backup and restore on the new server users databases...
this will not move the logins...
you could then script the logins out and recreate them on the destination
server... keep in mind you will require some login/user sync, as better
explained in
http://www.sqlservercentral.com/columnists/nboyle/fixingbrokenlogins.asp

as regard msdb objects... script them out too, and recreate tem form their
DDL sql scripts in the destination server...
you are not required to shut down the original server, this way... nor if
you can use Copy database EM wizard... you are required to temporary have
you server down, in someway, if you detach/reattach your user databases...
but I'd prefer the full backup/restore method in this particular scenario.. 

-- 
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm        http://italy.mvps.org
DbaMgr2k ver 0.9.1  -  DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Relevant Pages

  • Re: object level permissions being lost when migrating
    ... I first transferred the logins from the 2000 server to the 2005 server via ... I then restored the databases and re-synched the users via the ... Errors After Restoring Dump ...
    (microsoft.public.sqlserver.security)
  • Re: Need guidance
    ... Close all databases that are being shared by FM7 sever. ... Open FM7 pro 7 to execute a script that will import data from another file ... Open all databases in FM7 server to be shared. ...
    (comp.databases.filemaker)
  • Re: Move databases between servers
    ... What I'm trying to do now is to copy master. ... Now I'm reinstalling sql with identical version, and restore all databases ... Since the logins are in master and operators/agents are in msdb why I ... copy all database and log files to new server ...
    (microsoft.public.sqlserver.setup)
  • Re: Create role on 64 bit extremely slow
    ... working properlly (except role creation), except that any change in a role ... made the whole AS server slow/ not responding (also other databases). ... generate the script and execute the script. ...
    (microsoft.public.sqlserver.olap)
  • Re: AS2005 ... what is wrong with it?
    ... What I have seen is that the dev server is *faster* than the prod server ... And does your dev server also have all 6 databases with all these roles? ... When I restore the "PROV" i got approx 20 sec. ... Starting from an empty data folder should show if this theory is true ...
    (microsoft.public.sqlserver.olap)