Re: Move databases between servers

In our case, the "old" and "new" servers have the same name, so only 1 can be
connected to the network at a time, so we can't copy DTS or anything from 1
to another. I assume we can backup the system databases (including
distribution), disconnect the "old" server, connect the "new" server, and
then restore the system databases. Then attach the databases (detached on
external storage).

One problem is that I can't detach replicated databases, so I guess I'll
have to do a restore of that one, a couple of hours :-(

"Keith Kratochvil" wrote:

Its not strange that Microsoft suggests copying user databases and not the
system ones.
Tell me why you need to move master, msdb, and model?

Microsoft provides documentation on how to move users between servers.

When you restore (or attach) databases on your other server SQL Server adds
the appropriate information to master so that the databsaes will work --
just like on your primary server.

If you have lots of jobs defined you could try restoring MSDB. As I wrote
in my previous message:
"If you move your msdb database you will want to update originating_server
within the sysjobs table. Change the old server name so that it matches the
new server name." I am pretty sure that I have restored msdb from one
server to another in the past. I think that the only thing you have to
watch out for is the originating_server. You only need to move msdb if you
have lots of jobs/DTS packages. Then again, it should be easy enough to
re-create jobs and move DTS packages if you don't want to do anything witih
msdb.



--
Keith Kratochvil


"Tarek" <Tarek@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:44C48C93-98D5-45EE-9026-86BC8B94F90B@xxxxxxxxxxxxxxxx
Hi Keith,
maybe this is the longest way but it's very strange that microsoft
suggests
to copy only user databases and not system ones.
What will happen if I loose my primary server? What will be my contingency
plan? Should I be able to restore all the database backups to the new
server?
Now I'm in that situation. This is also a good way to discover how to face
a
such critical situation.
Thanks,


"Keith Kratochvil" wrote:

You are taking the long road to solving your problem.

After master has been restored check what is returned when you query
SELECT @@servername

It is easy to move DTS packages. You can save them as text files and
save
them into your new SQL Server. Operators are a breeze to set up, and
moving logins is a simple process using the Microsoft supplied stored
procedures that I referenced in a previous post.

--
Keith Kratochvil


"Tarek" <Tarek@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B080FDF6-BFE6-468C-A798-8C3A7A61CF0D@xxxxxxxxxxxxxxxx
Hi Keith,
thanks for your replies.
What I'm trying to do now is to copy master. I think this is less risky
because I'm sure I will not loose any login or dts or operators... I
encountered a problem because two sqlserver versions are different (old
is
with sp3, new is with sp4).
Now I'm reinstalling sql with identical version, and restore all
databases
starting with master. I will do something like:
1) stop sqlserver services
2) start in single user mode
3) restore master
4) restore all databases
5) install sp4
I hope this works.






.

Relevant Pages

  • 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)
  • Re: Copying WSS to another Server
    ... the data in sync I back the SQL databases up and restore them to the replica ... It already has WSS installed. ... > on one server and copy it to another for dev/test purposes, ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: AS2005 ... what is wrong with it?
    ... I have 6 OLAP databases on the server. ... When I restore the "PROV" i got approx 20 sec. ...
    (microsoft.public.sqlserver.olap)
  • Re: Exchange DR, what to do
    ... setup blank databases on theDRexchangeserver, ... data out of the RSG, it tries to restore it to the original server, ... the cluster exchange virtual server name was, ...
    (microsoft.public.exchange.admin)
  • RE: Moving SQL Server 2000 to a new machine
    ... In addition to all the CYA advise, you CAN move the system databases. ... Once you've got the new server at SQL2K, SP2, and the physical paths to ... > Recently I was tasked to move our entire SQL Server 2000 to a new server. ...
    (microsoft.public.sqlserver.server)