A newly restored db on an existing replication | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

A newly restored db on an existing replication

We are using SQL 2K5 with sp2. Say I have set up a transaction push replication on the publisher database (server A) to a subscriber database on a different server (server B). The distribution db is on the publisher server. The replication is working. Now I am ready to go live, can I just restore the most current db on top of the publishing database and then initialize the snapshot to replicate the new data to the subscriber database? Or do I need to redo all the publishing and subscription setup? wingman
to restore the db, you will have to stop the replication. which means you will have to set up the replication again. but no, you dont have to reinitialize the snapshot. just skip that part of the wizard and choose ‘data already exists’ option. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
If you restore the same the replicated database backup… your replication will work but when you restore with different database backup copy then it will not work… Best approach will be …
script your replication…
Restore the database.
Modify the scripts if the server name is different
Run the scripts…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
What if the mentioned most current database backup copy which I will use to do the restore is two days newer than the existing publishing db? If I do what I suggest by choosing ‘data already exist;, the subscriber database will not be in-sych with the restored database because the subscriber db is still two days old.
quote:Originally posted by ndinakar to restore the db, you will have to stop the replication. which means you will have to set up the replication again. but no, you dont have to reinitialize the snapshot. just skip that part of the wizard and choose ‘data already exists’ option. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
If you have relevant referential intregrity then the duplicate data will be ignored or atleast they are alerted in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>