5

We are in planning to upgrade from SQL Server 2008 R2 to SQL Server 2014, with the intention of using Availability Groups.

One of the secondaries will be used to do backups.

I have tested and found that I can do point in time restores as long as I have the copy-only full, and the chain of log backups that span the copy-only up to the time we want to restore to.

We do NOT use differentials, and have no plans to.

I cannot see any downside to this approach other than perhaps not being able to shrink the data file without a "normal" backup. We do not normally shrink our databases unless we do something to blow them up, and by then we are in intervention mode anyway.

So my question is, will I regret going this route?

1 Answer 1

1

It sounds like you've done your homework to a large degree. Clearly you're aware of the log chain implications for COPY_ONLY backups. My advice to you would be to ensure you have a clearly though-out and tested restore plan.

Without having tested your disaster recovery plan, you can never be certain it will work, and therefore it's not a plan.

If you can do standard, i.e. not COPY_ONLY backups, then do them. I might consider doing standard FULL backups on the primary node every so often just because you can never have too many backups.

2
  • That is pretty much what I was thinking as well. Maybe once a week/month do the "normal" just to reset the log chain periodically.
    – JohnH
    Apr 15, 2016 at 13:13
  • yep. good plan. The most important thing I can recommend is to ensure you test restore and recovery on an on-going basis so you know how to do it for real when the time comes.
    – Hannah Vernon
    Apr 15, 2016 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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