moving sql server new computer

Posted on 2005-04-21
Last Modified: 2010-03-19
I'm moving all my data to a new server.  If I name my name server exactly the same as the old and (stop all database services) and then copy all the files like master, model, msdb, distributions, etc to the exact same directories.  Does everything copy? Like server level settings, jobs, dts packages, alerts, operators, logins etc get copied across?
Question by:gotaquestion
    LVL 5

    Expert Comment

    1. Jobs need to be recreated (you can script them out and redeploy).
    2. Databases best be backed up and restored.
    3. DTS packages - don't think you can script them. Need to rebuild.
    4. Operators, alerts, and such - you can script out the DB server, and that should include all those details, so that you could rerun the scripts again.

    To script out server stuff- click "generate script" anywhere (e.g., table script or such), then on the rightmost tab, check all server related scripting (I just upgraded to Yukon, and don't have the exact tab name).

    LVL 21

    Expert Comment

    3. DTS packages, you can open on  old server, then re-save on new server if both servers are up and running.  Easier than re-creating :-)
    LVL 1

    Author Comment

    LVL 5

    Accepted Solution

    The process he is describing is identical to manually performing a ghost of one machine over the other, pretty much.
    LOL. If the smallest change that may affect SQL server is present between the new and old server, his prescription will not work.

    What he does is typical in large companies, where ALL the servers are EXACTLY identical.
    Very difficult to achieve, and it is a highly non-robust solution.
    LVL 44

    Assisted Solution

    by:Vitor Montalvão
    I made some SQL Server moves, and it's not hard.

    Here's the steps I usually use:
    1) Stop old SQL Server and copy all user databases and master databases to the new one server
    2) Start again SQL Server on old server.
    3) Backup old msdb database
    4) Attach master database from the old server in the new SQL Server. Give it another name (ex: master_old)
    5) Copy sysxlogins from master_old to master (this recreate all the users on old SQL Server at the new one)
    6) Attach all user databases from old server to the new one (ensure that database id's are the same)
    7) In old SQL Server open all DTS and save them to the new SQL Server (Save as...)
    8) In new SQL Server restore old msdb database over new msdb database (this recreate the jobs)
    9) If you have any job that execute DTS you need to recreate them because DTS id's will be different in the new server

    Good luck

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now