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 am setting up transactional replication for the first time and am working through my backup policies I need to set.

Is my Publisher database my actual source database? Or is it a new database that gets created when I create the new publication?

I know our production database is currently backed up regularly, Im not sure if I also need to back up the 'Publication` as well once it is created? enter image description here

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Yes, your publication database is your source database.

There are some considerations when backing up the publication database, as well as other replicated databases, such as the distribution and subscription databases. This is covered in Strategies for Backing Up and Restoring Snapshot and Transactional Replication.

share|improve this answer
    
Thanks. My next question is if we have a massive failure and need to restore the publisher to a point in time 24 hours ago, do we need to also restore the distributor database to a point 24+ hours ago? I think the answer is yes to to ensure the distributor only has records that are also in the publisher. There is no way the distributor to know the publisher has been restored in other words. –  ProfessionalAmateur Feb 11 at 23:46
    
That is correct, however, even if you restore both the publication and distribution databases, the subscription database(s) will potentially be inconsistent. At that point you may want to also drop and recreate the subscription and initialize it from the publication backup. –  Brandon Williams Feb 12 at 0:44

The "source" database is being referred to when the "publisher" database is discussed. Take a look at the below diagram from this reference, it shows it pretty well:

enter image description here

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.