2

Scenario:

  • DB is replicated in two availability zones (for high-availability)
  • DB is backed-up daily in geographically separate location (for disaster recovery)

App-level user events are stored in the DB (for app-level audit/history). This means point-in-time restore is probably not going to happen on the DB-level but rather on the app-level, unless a rouge app user deliberately messed with the whole DB, making DB-level restore more practical.

My question is, what's a sensible retention policy for the daily database backups? E.g., Does it makes it sense to store 30-days worth of backups? What's the general best practice in this typical scenario?

2
  • That's going to depend on the nature of the data. Are we talking about financial data, or high scores for a game? Do you have a SLA with your customers? May 23, 2019 at 18:39
  • @JonofAllTrades Well just assume data is important for business operations. Thing is, backup runs daily. In case disaster happens, the business would want the latest available backup, so that means yesterday's backup is more valuable than the 2nd or 30th day because the most recent state of data is not in those backups. What goes in my head is that anything beyond day 1 backup is sort of "useless" so I'm asking what's the general practice on things like this.
    – IMB
    May 23, 2019 at 19:01

1 Answer 1

5

There are three questions that drive answers here:

  • How much space is the business willing to pay for to store backups?
  • Are there legal requirements to store backups for a certain period of time?
  • How often are you checking for corruption (running DBCC CHECKDB)?

For larger amounts of data, the first question usually answers the question straight away. If there are legal requirements, that can also answer things pretty quickly.

If neither of those impose a limit, then the third question is a good starting place.

Say you run DBCC CHECKDB and find corruption -- you're going to want a full backup to use in case you can't fix things in-place. If you're running it once a week, then keeping two weeks worth of backups is probably a good idea.

If you're not running CHECKDB, well, go do that instead of this.

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