Re: Move databases between servers

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: 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: select from one db only
    ... Those databases are available to anyone who can login to the server because ... You should never remove guest from master ...
    (microsoft.public.sqlserver.security)
  • Re: Move databases between servers
    ... It is possible to restore master, but it is a complicated process and it ... databases just as you are planning on doing. ... 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: MSDB and MASTER DB
    ... For these volumes I would use transactional replication if possible. ... In master your linked servers and logins live. ... Your msdb databsae ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: restore master database not working
    ... U have restored master, however your other system databases are not being ... location on the new server and then run sp_attach_dbfor all the ... if you wish to restore your SQL agent Jobs then you can ...
    (microsoft.public.sqlserver.server)
Loading