none
Detach published database from one server and attach to another

    Question

  • Hello,

    I need to move the replicated database from publisher to another server. It is on SQL 2008 Ent Edition. The server name will be different.

    Do I have to drop publications (they will refer the old server name)?

    Do I stop distribution agent and log reader agent?

    Do I migrate Distribution database?

    Do I disable database for replication to be able to detach and attach?

    Please help

    Thanks,


    • Edited by sqlpal2007 Tuesday, November 12, 2013 8:07 PM
    Tuesday, November 12, 2013 8:06 PM

Answers

  • You can script the replication objects prior to dropping the publication and subscriptions on the existing server.  Right-click the publication and subscriptions, or the Replication folder -> Generate Scripts...  Please see the section To script an object from Management Studio and To script multiple objects from Management Studio in How to: Script Replication Objects (SQL Server Management Studio).

    You will need to do a find/replace for the old server name with the new server name.

    If you don't bring over the distribution database then you will need to configure distribution prior to creating the new publication.  To configure publishing and distribution, see Configure Publishing and Distribution.

    By default, article properties are set to Drop existing object and create a new one.  If you initialize your subscription at Server B, you might get the error Cannot drop table 'Foo' because it is published for replication...  You can specify not to initialize the subscription at Server B, or recreate the publication and subscription at Server B and Server C.

    I suggest setting this up and practicing in your test environment prior to deploying to production.


    Brandon Williams (blog | linkedin)

    Tuesday, November 12, 2013 9:19 PM
    Moderator
  • Hi,

    You need to drop the Publication and the subscription for the replication that was configured. The distribution database can stay if the distribution database is serving other replication setups else it will be good if the distrubution database is also dropped.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 12, 2013 8:30 PM
  • Stop SQL Server Agent

    Drop the publications and subscriptions on the existing server.

    Detach database (will it allow to detach since replicated)

    Attach database to new server

    Configure database for publisher and configure distribution

    Create publications and subscriptions with 'replication support only'

    My problem is - how do I remember current replication setup as not all the tables and columns are replicated. Also table filters are applied on tables. Can I script the existing publications and subscriptions? Will I be able to deply the script as it is? May be not because it will have reference to the old server name.

    I will be bringing the system databases to new server except distribution database. When does distribution database get created?

    Another thing is Server A is a publisher which is moving to new server. But A replicates to B and then B replicates to C. I guess if I drop publications A and subscriptions for B, it shouldn't break anything in C as I am not dropping any tables from B.

    Thanks,

    Tuesday, November 12, 2013 9:00 PM
  • It will be in the @pre_creation_cmd parameter. Accepted values are drop (none), delete, or truncate.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, November 13, 2013 5:12 PM
    Moderator

All replies

  • Hi,

    You need to drop the Publication and the subscription for the replication that was configured. The distribution database can stay if the distribution database is serving other replication setups else it will be good if the distrubution database is also dropped.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 12, 2013 8:30 PM
  • If the new server name is different than old server name then you will need to drop and recreate the publication and subscriptions.

    If the drop and recreation of the publication and subscriptions is performed during a time when no activity is occurring, e.g. a maintenance window, then you can utilize the option 'replication support only' when recreating the new subscriptions to avoid sending down a new snapshot.

    If the new server has the same name as the old server name then you can backup the publication, msdb, distribution, and master databases, along with the service master key, and restore them on the new server.  Use the keep_replication switch when doing the restore.  This approach avoids having to drop and recreate the publication and subscriptions.


    Brandon Williams (blog | linkedin)

    Tuesday, November 12, 2013 8:31 PM
    Moderator
  • Stop SQL Server Agent

    Drop the publications and subscriptions on the existing server.

    Detach database (will it allow to detach since replicated)

    Attach database to new server

    Configure database for publisher and configure distribution

    Create publications and subscriptions with 'replication support only'

    My problem is - how do I remember current replication setup as not all the tables and columns are replicated. Also table filters are applied on tables. Can I script the existing publications and subscriptions? Will I be able to deply the script as it is? May be not because it will have reference to the old server name.

    I will be bringing the system databases to new server except distribution database. When does distribution database get created?

    Another thing is Server A is a publisher which is moving to new server. But A replicates to B and then B replicates to C. I guess if I drop publications A and subscriptions for B, it shouldn't break anything in C as I am not dropping any tables from B.

    Thanks,

    Tuesday, November 12, 2013 9:00 PM
  • You can script the replication objects prior to dropping the publication and subscriptions on the existing server.  Right-click the publication and subscriptions, or the Replication folder -> Generate Scripts...  Please see the section To script an object from Management Studio and To script multiple objects from Management Studio in How to: Script Replication Objects (SQL Server Management Studio).

    You will need to do a find/replace for the old server name with the new server name.

    If you don't bring over the distribution database then you will need to configure distribution prior to creating the new publication.  To configure publishing and distribution, see Configure Publishing and Distribution.

    By default, article properties are set to Drop existing object and create a new one.  If you initialize your subscription at Server B, you might get the error Cannot drop table 'Foo' because it is published for replication...  You can specify not to initialize the subscription at Server B, or recreate the publication and subscription at Server B and Server C.

    I suggest setting this up and practicing in your test environment prior to deploying to production.


    Brandon Williams (blog | linkedin)

    Tuesday, November 12, 2013 9:19 PM
    Moderator
  • Will drop publication and subscription delete the data or drop the table at subscriber?

    Wednesday, November 13, 2013 4:22 PM
  • No, it will not.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, November 13, 2013 4:49 PM
    Moderator
  • Where can I see the 'Action if name is in use' property of published articles (Truncate all data in the existing object, Truncate data by filter, drop tbale) in the create article script?
    Wednesday, November 13, 2013 5:04 PM
  • It will be in the @pre_creation_cmd parameter. Accepted values are drop (none), delete, or truncate.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, November 13, 2013 5:12 PM
    Moderator