moving sql server new computer

Posted on 2005-04-21
Medium Priority
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

Expert Comment

ID: 13836102
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

ID: 13836199
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 :-)

Author Comment

ID: 13836378

Accepted Solution

obahat earned 252 total points
ID: 13836436
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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 248 total points
ID: 13841867
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 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