Monday, March 12, 2012

replication and data move

Hi,
I'm planning to move MS SQL Server 2000 instance to another server. I know
that the easiest way is to copy data directory from one machine to another
and set new server name to the old one. Because of some reasons I can't do
it in thisy way. Besides that I don't watn to move all databases but some of
them. Unfortunately, some of these databases are replicated (part of them is
published and another part is subscibed).
How to move MSSQL instance to another machine keeping replication working
properly without starting replication from scratch? Is there way to restore
database (published or subscribed) with already set replication?
Thanks
Marcin
> Hi,
> I'm planning to move MS SQL Server 2000 instance to another server. I know
> that the easiest way is to copy data directory from one machine to another
> and set new server name to the old one. Because of some reasons I can't do
> it in thisy way. Besides that I don't watn to move all databases but some
of
> them. Unfortunately, some of these databases are replicated (part of them
is
> published and another part is subscibed).
> How to move MSSQL instance to another machine keeping replication working
> properly without starting replication from scratch? Is there way to
restore
> database (published or subscribed) with already set replication?
> Thanks
> Marcin
You can't do it. According to SQL Server Books Online:
Restoring Backups of Replicated Databases to a Different Server or Database
When you restore a backup of a replicated database to a server or database
other than the one on which it was created, your replication settings
cannot be preserved. For publishing databases and merge subscribing
databases, a full restore of the database and logs is followed by an
automatic removal of replication meta data from the database when the
database or server you restore to differs from the one on which the backup
was created.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

No comments:

Post a Comment