Replicated database recovery… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Replicated database recovery…

Hi, I’m looking for good solution in case when one of my replicated databases will be damaged.
We have 16 servers replicating to main server.
Replication is in transactional mode.
Replication is in continous mode (i’m not sure that this is a good idea)
All subscriptions are "push".
All servers are in different locations and they have 0.5 Mbit internet connection.
We are making backups (log backups) every 2 hours. Once a week we are making full backup. I need to make sure that I can recover any database with no need to make any snapshots.I’ve setup on distributor "Transaction Retension" to 24 hours. 1. Situation when database has been damaged on subscriber site.
We will restore selected database on subscriber to the latest backup.
Replication should be reestablished? 2. Situation when database has been damaged on publisher site.
We will restore selected database on publisher and on subscriber (subscriber can have more data than restored pusblisher database).
Replication should be reestablished?
Do I need to recover on publisher distribution and msdb databases? Distribution Agent options
-BCPbatchsize 100000
-Commitbatchsize 100
-commitbatchthreshold 1000
-histlevel 1
-keepalivemessageinterval 300
-login timeout 30
-maxbcpthreads 1
-maxdeliveredtrans 0
-pooling interval 10
-querytimeout 300
-transactionsperhistory 100 Log Reader Agent
-historyverboselevel – 1
-login timeout 30
-pooling interval 10
-querytimeout 300
-readbatchsize 500
1. See this link that explains the feature.
2. Ensure to have all the databases such as master, msdb and publisher backup in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
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.