Want to discuss on DR plan or Backup restore plan for transactional replication (here I will discuss on PUSH T replication).
Distribution database plays a very important role in Transactional replication.
The DR plan would be:
1. make a log shipping enable for the publisher
2. Backup plan
Take backup of all system databases (once a day -full backup).
a. master (logins)
b. msdb (jobs)
c. distribution (replication transactions).
d. publisher...(base database)
Take the incremental backup (transactional backup/Differential backup) at regular basis for the following:
a. Publisher database.
b. Distribution database.
Now you have to enable the "issyncwithBackup" .
If anything goes wrong on the publisher server. we have to rebuild the publisher server which should also have the transactions which are not transferred to the subscriber(which will be their at "Distribution" database..
* Server name should be same
Restore the Publisher Server:
install the OS and Sql server.
a.Restore the master for logins.
b. Restore the msdb for jobs.
c. Restore the distribution database for transactions which are done on publisher but not replicated to subscriber.
d. Restore the publisher database.
-when we restore the msdb database the the jobs and replication monitor will appear on the Enterprise manager or Management studio.
-when we run the distributor agent the transactions which were their on distribution database will transfer to subscriber.
- after restoring the publisher database. you must be good for work.
3. The another process would be (very difficult).
Create a script
a. bcp out the data from source tables.(conditional last 60 days data).
b. bcp in the data into target server with different server.
c. compare the data. if not exist "insert".
d. compare if changes "update"
e. compare if removed "delete"
f.take care of identity seed.
check carefully ...
*Disclaimer: This posting is provided AS IS with no warranties, and confers no rights.