Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to Restore the replicated database on different server and resume the replication from there. I have distributor and subscriber running on the same server. when i restore my replicated database on different server i don't want to Re-initialize the subscription.

I know i can setup the replication with manual initialize but in that case data that has not transferred to the distributor and backed up will not be replicated. i want to replicate those data as well.

share|improve this question

2 Answers 2

If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. After the restore to the new location, you will have to recreate the publications and subscriptions. BOL link.

share|improve this answer
up vote 0 down vote accepted

you have to follow below steps to resume your replication from the DR env:

  1. If the sync with backup option is not set on the publication database, execute sp_replicationdboption '', 'sync with backup', 'true'. For more information, see sp_replicationdboption (Transact-SQL).

  2. Configure log shipping for the publication database. For more information, see Log Shipping Deployment.

  3. If the Publisher fails, restore the last log of the database to the secondary server, using the KEEP_REPLICATION option of RESTORE LOG. This retains all replication settings for the database. For more information, see Failing Over to a Log Shipping Secondary and RESTORE (Transact-SQL).

  4. Restore the msdb database and master databases from the primary to the secondary. For more information, see Considerations for Restoring the model and msdb Databases and Considerations for Restoring the master Database. If the primary was also a Distributor, restore the distribution database from the primary to the secondary.

    These databases must be consistent with the publication database at the primary in terms of replication configuration and settings.

  5. At the secondary server, rename the computer and then rename the Microsoft SQL Server instance to match the primary server name. For information about renaming the computer, see the Windows documentation. For information about renaming the server, see How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server and How to: Rename a SQL Server Failover Cluster Instance.

  6. At the secondary server, restore the service master key that was backed up from the primary. For more information, see RESTORE SERVICE MASTER KEY (Transact-SQL).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.