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?

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
JohnH
  • 433
  • 4
  • 10

1 Answers1

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.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323