how to turn the sync with backup option on distribution database? - transactional replication

Question

Hi folks,

I have the transactional replication enabled for the reporting purpose.I need to plan for the disaster recovery also.I came across the strategy for backing up the distribution database.Can any one help me in which way or where in can set this option of sync with backup on the distribution database ?  

Answer 1

sp_replicationdboption 'distribution','sync with backup','true'

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Hi ,

Hilary has already given the command.

Just to add to this, make sure that you have proper backup strategy for the distribution database , otherwise the sync with backup option can cause unexpected growth of your publisher db's transaction log.

Regards

Ashwin

Answer 3

Hilary,

If possible can you please help me with the below thread ?

http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/92706b42-6e20-4e53-989a-09211a56d553

Answer 4

You do not actually backup the "distribution" database.  The "distribution" database only holds temporary unreplicated transactions.  You need to backup your published database and init it using that backup.