2

In Log shipping configuration - the primary database must be either in Full or Bulk-Logged recovery model. However, is there any requirement of recovery model of the secondary database?

Example: Assuming both primary and secondary databases are in full recovery model, if there is a failover and the secondary becomes the new primary, we could change the db recovery model.

Also refer to: https://www.microsoftpressstore.com/articles/article.aspx?p=2832586&seqNum=3 and search for "Log shipped databases have to use the full recovery model." - is this correct?

Improve this question
1
  • You can definitely make FULL COPY_ONLY backups of the secondary, but really your concern is for the primary. It may result in loss of data to switch
    – clifton_h
    Oct 10, 2018 at 13:15

1 Answer 1

Reset to default
9

The recovery model for the secondary will be the same as the primary database, because you're backing up the primary, restoring it on the secondary node using NORECOVERY/STANDBY, and then restoring subsequent log files to it.

In a log shipping configuration, you cannot make changes to the secondary database. If you could, then the databases would be out of sync, defeating the purpose of log shipping as a DR solution.

Edit: The Microsoft documentation is fairly comprehensive regarding the basics of log shipping and the requirements for it.

Improve this answer
8
  • In a scenario of a failover, the secondary becomes the new primary (assumign both are in full recovery model). At this point, we could change the recovery model
    – variable
    Oct 10, 2018 at 9:48
  • 3
    What benefit is there to changing the recovery mode when you've failed over to the secondary node? There's no need to, and changing it to SIMPLE WILL break the log chain when you do a database backup.
    – Stephen Falken
    Oct 10, 2018 at 9:52
  • Refer to: microsoftpressstore.com/articles/… and search for "Log shipped databases have to use the full recovery model."
    – variable
    Oct 10, 2018 at 9:52
  • I am not sure of purpose/benefits, I have only raised this question as I got confused when read the above link :)
    – variable
    Oct 10, 2018 at 9:57
  • @variable - I read that article, and yes - the database needs to be set to Full/Bulk-Logged recovery for Log Shipping. When you fail over to the secondary, the recovery model stays the same. You don't change it, because you want to keep taking transaction log backups so if there's a problem on the (now) primary node, you can restore to a point in time. See Shankys comment for greater clarity regarding the recovery model.
    – Stephen Falken
    Oct 10, 2018 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.