SQL Server Performance

Restore WITH KEEP_REPLICATION

Discussion in 'Performance Tuning for SQL Server Replication' started by ginoitalo, Mar 6, 2004.

  1. ginoitalo New Member

    I created a backup of the biz database from our Main Server.
    I went to our test server, imported it for the first time,
    created a publication from it, pushed it out and then executed the following command on the test server:

    RESTORE DATABASE biz
    FROM DISK = 'C:iz.bak'
    WITH KEEP_REPLICATION,
    MOVE 'B_Data' TO 'C:iz.mdf' ,
    MOVE 'B_Log' TO 'C:iz_log.ldf'

    After that, the biz DB on the test server still had the 'shared' hand on it's DB icon (which it should) but the publication was gone and the subscriber's merge agents were still there but then as soon as I re-created the publication of the same name they were gone too.

    What did the WITH KEEP_REPLICATION even do?

    Is there a way to restore AND keep the publication, subscriptions, merge agents, etc... ?
  2. TALAT New Member

    MSDB contains the agents and repl jobs. You need to restore MSDB too on ur test server.

  3. ginoitalo New Member

    restore MSDB on the test server from the msdb of the Main server ?

    then that will overwrite other agents and jobs on the test server, correct ?

    but the publication is still deleted.
  4. satya Moderator

    KEEP REPLICATION instructs the restore operation to preserve replication settings when restoring a published database to a server other than that on which it was created.

    Try to recreate those involved jobs on target server which is a part of reference by Talat.
    Better to run sp_helpreplicationdboption from QA and see the results, try to refresh the database after the restore, sometimes GUI applications will have flaky results.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page